Forum Discussion
Francisco77
Oct 18, 2023Copper Contributor
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 "."?
The result should be:
30021
3205
3205
254444455
225541225
Thank you so much,
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, ".")) )
- SGGusauCopper ContributorPower Query's split by delimiter would have been the ideal solution, but you prefer formula.
- PeterBartholomew1Silver ContributorA Power Query solution would be particularly appropriate if it could be used to clean the data prior to loading to the spreadsheet. A classic ETL task.
- SGGusauCopper ContributorAnd that's where PQ excels. The one column data, in this example, would easily get TRIMED, CLEANED and SPLIT, all using the user interface. 3-5 clicks and a job well-done can be loaded back onto the worksheet. The column can grow as long as possible with similar "dirty" data and all it takes is hitting the Refresh button.
- Patrick2788Silver Contributor
- Francisco77Copper Contributor
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).
- Patrick2788Silver ContributorDo you happen to have a screen capture of your sheet? I'm not sure all the characters are coming over in your post.
- Detlef_LewinSilver Contributor
- Francisco77Copper ContributorThanks but when I have numbers with "." It does not take them as numbers with decimals given the language of my laptop. Numbers with decimals are with "," exm: 233.2