Formula extract digits

[Francisco77]

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,

[#2]

@Francisco77 

INT(A1)

ROUNDDOWN(A1,0)

QUOTIENT(A1,1)

[#3]

@Francisco77 

Try this:

=TRUNC(A1:A5)

[#4]

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

[#5]

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

[#6]

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

[#7]

@Patrick2788 

Yes, Attached the picture.

Thanks,

Francisco

[#8 · Accepted 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, "."))
  )

[#9]

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

[#10]

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

[#11]

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.

[#12]

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.