Forum Discussion

GavinMc460's avatar
GavinMc460
Copper Contributor
Dec 09, 2020
Solved

Reducing multiple decimal points in a value

Hi All Would anyone know of a formula in Excel or PowerBI where we can reduce a value of multiple decimal points to just one?  I have tried all the usual formulae in Excel but cant seem to locate th...
BI & Data Analysis
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Dec 09, 2020

    GavinMc460 Perhaps this:

    =LEFT(A1,FIND(".",A1,3)-1)

    where A1 contains the version number and the first period never occurs after the third character in the text string.

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 09, 2020

GavinMc460 

What is the logic, trim everything starting from second found dot?

  • GavinMc460's avatar
    GavinMc460
    Copper Contributor
    Dec 09, 2020
    That was it exactly Sergei, many thanks.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 09, 2020

      GavinMc460 

      To be more safe with number of digits between dots I'd modify as

      =LEFT(A1,FIND("@",SUBSTITUTE(A1,".","@",2),2)-1)
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 09, 2020

    SergeiBaklan Ooops! Now my screen had not refreshed. So I didn't see your response pointing in the same direction I was thinking.

Resources