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

     

Francisco77's avatar
Francisco77
Copper Contributor
Oct 18, 2023
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

Share

Resources