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 

 

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
    Oct 18, 2023
    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

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

         

Resources