Forum Discussion
Solved
Formula extract digits
Hi All, I have a series of data in an excel column: 30021 3205.1 3205.2 254444455 225541225 Is it possible, using a formula, to extract all the digits from the left up to the "."? Th...
The terms with the embedded character that is not a decimal separator (radix) could be separated using
= NUMBERVALUE(TEXTBEFORE(data, ".",,,1))where the final 1 ensures that, in the absence of the separator, the entire string is returned. The integer part of numbers with a decimal separator could then be returned using INT.
= INT(NUMBERVALUE(TEXTBEFORE(data, ".",,,1)))Alternatively you could test to distinguish numbers from text strings and apply different rules according to the case.
= IF(ISNUMBER(data), INT(data), NUMBERVALUE(TEXTBEFORE(data, ".")) )
Thanks but when I have, for example, 565.1 it works. When I have 565.1 it gives an error, since the first takes it as decimals due to the language configuration of my laptop, the second (when there is a "." it does not take it as decimal).
- Do you happen to have a screen capture of your sheet? I'm not sure all the characters are coming over in your post.
