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

     

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

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 18, 2023

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

     

Share

Resources