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

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

@Francisco77 

 

INT(A1)

ROUNDDOWN(A1,0)

QUOTIENT(A1,1)

 

@Francisco77 

Try this:

=TRUNC(A1:A5)

@Patrick2788 

 

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

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
Do you happen to have a screen capture of your sheet? I'm not sure all the characters are coming over in your post.

@Patrick2788 

 

Yes, Attached the picture.

 

Sin título.png

 

Thanks,

 

Francisco

best response confirmed by Francisco77 (Copper Contributor)
Solution

@Francisco77 

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

 

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

 

Power Query's split by delimiter would have been the ideal solution, but you prefer formula.
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.
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

@Francisco77 

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

 

View solution in original post