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)

Resources