Using copied in data in calc, but some numbers are treated as text as they are expressed as k
Hello
use Edit -> Find & Replace according to screenshot:
If the answer is correct or helped you to fix your problem, please click the check mark (
) next to the answer.
Note: with that replacement it works only if the current locale’s decimal separator is . dot, otherwise the formula’s dot will have to be exchanged with the locale’s decimal separator.
Btw, the string to find should be ^([:digit:]+)\.([:digit:]+)k$ so that the first group of digits is anchored to the cell start, and the . is actually a literal dot not just any character, and the trailing k is anchored to the cell end (and it doesn’t need to be grouped). Otherwise the expression might match more than intended.
It also helps to actually write out or better paste such expressions in an answer so the user can just select and copy.
2k wouldn’t be matched, not even with your expression, but 123k would and yield 1300.
Just for clarification: I have been talking about my expression not yours…
Probably ^([^k]+)k$ is a better regular expresssion at all.
Depends on what data you process, if it’s guaranteed to contain only digits and decimal separator and a trailing k then it’s ok, otherwise it would match more than desired, in fact anything (not containing k) ending with a k.
If it’s only k and either a string ending with k or an actual number in A1 then
=IF(RIGHT(A1;1)="k";NUMBERVALUE(LEFT(A1;LEN(A1)-1);".")*1000;A1)
Copy down for a column of data.
Thank you just what I wanted.