Forum Discussion

Francisco77's avatar
Francisco77
Copper Contributor
Oct 18, 2023
Solved

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 "."? Th...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Oct 18, 2023

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

Resources