Forum Discussion

Francisco77's avatar
Francisco77
Copper Contributor
Oct 18, 2023

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,

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

     

  • SGGusau's avatar
    SGGusau
    Copper Contributor
    Power Query's split by delimiter would have been the ideal solution, but you prefer formula.
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      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.
      • SGGusau's avatar
        SGGusau
        Copper Contributor
        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.
    • Francisco77's avatar
      Francisco77
      Copper Contributor

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

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

Resources