CODEWITHSUNDEEP
excelexcel-formula
Howeitzer
Howeitzer

Reputation: 220

Excel formula to delete a character from a string then replace another character with the same character that was deleted

I have a number of coordinates and wish to remove a "." separator, then replace a "," decimal with the standard UK format "." character.

e.g. 9.811.365,054 needs to be 9811365.054

I initially used a nested substitute formula but this also removed the "." leaving only numbers i.e. 9811365054

Upvotes: 0

Views: 281

Answers (1)

user2140173
user2140173

Reputation:

In VBE in the Immediate Window ctrl+g try

? replace(replace("9.811.365,054",".",""),",",".")
9811365.054

So pretty much you just nest two functions; first you replace all the dots with "" (nothing) and then replace the comma with a dot.

and an equivalent formula would be =SUBSTITUTE(SUBSTITUTE(A1,".",""),",",".") assuming A1 is 9.811.365,054

Upvotes: 3

Related Questions