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

     

Francisco77's avatar
Francisco77
Copper Contributor
Oct 18, 2023

Patrick2788 

 

Yes, Attached the picture.

 

 

Thanks,

 

Francisco

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
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, "."))
  )

 

Resources