Remove all but first occurence of a character

[rob_ford16]

I want to be able to remove all but the first period in a text string.

Example:

Original Cell Content: 20.42.00.23

What I want it to be: 20.420023


I feel like I should be using something in conjunction with the =SUBSTITUTE, but not sure what...

Thanks!

[Pete_UK]

Try this:

=LEFT(A1,FIND(".",A1)) & SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(".",A1)),".","")

assuming your value is in A1.

Hope this helps.

Pete

[rob_ford16]

Omg thank you a thousand times! You have just saved me a ton of time! Works perfectly!

[martindwilson]

slightly shorter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","^",1),".",""),"^",".")