SUBSTITUTE AND REPLACE in the one cell

[MattKoleczko]

Probably super simple, but every combination I try fails.

I basically have a list of phone numbers I need amended - country code removed and replaced with a 0, and all spaces removed i.e.
I have +61 412 345 678, and I want it to read 0412345678

I can only do this if I substitute the spaces in once cell and then replace the first three characters in the second cell, which works but I'm a nerd and want to achieve this in one cell.

Thanks legends!

Matt

[AliGW]

How about this?

=TEXT(TRIM(MID(A1,FIND(" ",A1)+1,11)),"000000000")

[Haluk]

Substitute ";" with "," depending on your language settings of your Excel and try this;

Please Login or Register  to view this content.

[bebo021999]

If you dont want to use other cell for replacing, try to use Find and replace instead
Highlight the range, Ctrl-H:
step 1: Find what: +61
Replace with: Leave it blank
OK
step 2: Find what: blank character (hit spacebar )
Replace with: Leave it blank
OK

[Fluff13]

Another option
=TEXT(SUBSTITUTE(REPLACE(A2,1,FIND(" ",A2),"")," ",""),"0000000000")