CODEWITHSUNDEEP
excelexcel-formula
FuriousD
FuriousD

Reputation: 119

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

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?

Upvotes: 0

Views: 240

Answers (2)

JvdV
JvdV

Reputation: 75930

Try:

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

Old Excel versions:

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

Upvotes: 5

Jos Woolley
Jos Woolley

Reputation: 9062

Excel 2013:

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

Upvotes: 2

Related Questions