I imported a CSV file. One column for example contains the following text:
212,524 KB
12,321 KB
158,689 KB
18 KB
I only want the numerical part to eventually sum the column. I thought I was close with =RIGHT(C212,3) but no cigar.
Alternatively:
Format the cells as #,##0 KB, you will see an apostrophe appear at the beginning of the cell denoting the cell as text. Select the column, open Find and Replace (Ctrl+H). In the Find field enter ^. in the Replace field enter & , make sure that Regular expressions and Current selection are both ticked, then press Replace All. You will have numbers formatted with [space]KB
Cheers, Al
BTW. You could have done it the way you were attempting, you need the leftmost text however, not the right.
You want all the left text except the last 3 characters, [space]KB, so we need to find the length of the text we want to keep, LEN(C212)-3 will give us that length. So we enter =LEFT(C212;LEN(C212)-3). Now we have some text that looks like a number. So we get the value giving a final formula of =VALUE(LEFT(C212;LEN(C212)-3))
2 Likes
It is better to set the criterion this way:
.*
which means: find everything. When replacing everything found with itself, Calc evaluates the value again. The apostrophe disappears. The number becomes numeric.
1 Like
Yup. That also works with same result.
This is Answer, also.
Not sure of protocol here, but sincere thanks to all for the help and guidance. Very impressed with all the methods even if I was being reminded of how little I know 
Choose one answer/comment (the one that best solves your question), and mark it as Solution.
Instead, as the textual data uses the comma decimal separator, use NUMBERVALUE(...;",") with comma decimal separator, this way it is safe to load and evaluate the document’s text in any locale with any decimal separator.
I think that here comma is the thousands separator.
See, confusion everywhere… so, define the separator(s) to be used.
1 Like
I don’t think KB take a decimal separator, the subdivision is B and I think it is always 1024 B to a KB.
The reason I suggested to keep the KB number format was to show that it is KB and to take care in any future calculation, e.g. 1000KB = 1 MB for some things (marketing mainly) and 1024KB = 1MB for others. I did worry that MB and GB would appear in the list so second suggestion, but @LeroyG or @PKG answer does that job better.
EDIT: This way you need an auxiliary column, as suggested in your question. /EDIT.
If data is in cell A1, type in another cell: =REGEX(A1;"\D";"";"g")
See LibreOffice Help on REGEX and ICU Regular Expressions (for \D).
3 Likes
I think this removes the decimal point as well. Not sure how to do regex to remove text and keep point. In my case MB have decimal point.
| Used | Cost | Regex | Right (used,2) |
|---|---|---|---|
| 6KB | �0.00 | 6 | KB |
| 1KB | �0.00 | 1 | KB |
| 666KB | �0.00 | 666 | KB |
| 17KB | �0.00 | 17 | KB |
| 735KB | �0.00 | 735 | KB |
| 170KB | �0.00 | 170 | KB |
| 4KB | �0.00 | 4 | KB |
| 1.16MB | �0.00 | 116 | MB |
| 15.60MB | �0.00 | 1560 | MB |
| 3.48MB | �0.00 | 348 | MB |
| 8KB | �0.00 | 8 | KB |
See snip of data.
Hallo
Decimal-point:
=REGEX(A2;"\d+\.?\d+")
or Decimal-comma
=REGEX(A2;"\d+,?\d+")
1 Like