Remove all except 1 dot from text string and convert into number-CodePudding

I have a text string which reflects thousand separators as a dot but also the decimal. I want to convert this to a number but retain the decimal.

1.056.865.39  should be 1056865.39
6.685.3 should be 6685.3

I've tried substitute and replace but I can't work out how to retain the last decimal. Any ideas how to do this in excel?

CodePudding user response：

Try:

=SUBSTITUTE(TEXTBEFORE(A1,".",-1),".",) TEXTAFTER(A1,".",-1)/100

Old Excel versions:

=NUMBERVALUE(SUBSTITUTE(D13,".",",",LEN(D13)-LEN(SUBSTITUTE(D13,".",))),",",".")

CodePudding user response：

Excel 2013:

=SUBSTITUTE(A1,".","")/10^(1 (LEFT(RIGHT(A1,3))="."))