Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Formula extract digits

Copper 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,

11 Replies

# Re: Formula extract digits

``````INT(A1)

ROUNDDOWN(A1,0)

QUOTIENT(A1,1)
``````

# Re: Formula extract digits

Try this:

``=TRUNC(A1:A5)``

# Re: Formula extract digits

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).

# Re: Formula extract digits

Thanks 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

# Re: Formula extract digits

Do you happen to have a screen capture of your sheet? I'm not sure all the characters are coming over in your post.

# Re: Formula extract digits

Yes, Attached the picture.

Thanks,

Francisco

best response confirmed by Francisco77 (Copper Contributor)
Solution

# Re: Formula extract digits

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, "."))
)``````

# Re: Formula extract digits

Afterthought:

If you have the misfortune to be using a version of Excel other than 365 (or maybe 2021), you would need to use LEFT and either trap the errors or test the data

``= INT(IFERROR(LEFT(data, SEARCH(".", data) - 1), data))``

# Re: Formula extract digits

Power Query's split by delimiter would have been the ideal solution, but you prefer formula.

# Re: Formula extract digits

A 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.

# Re: Formula extract digits

And 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.
1 best response

Accepted Solutions
best response confirmed by Francisco77 (Copper Contributor)
Solution

# Re: Formula extract digits

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, "."))
)``````