Converting telephone number

[paulsolar]

Hi All

I'm after a one line formula to do two jobs

We receive telephone number in this format 0123-4567891 and I'd like to convert them to this format 01234 567891.

I can use substitute the "-" with this =SUBSTITUTE(D2,"-","") in helper column 1 and and then get the format correct in helper column 2 (Y) using =TEXT(Y2,"##### ######")

I'd like to do it all with one formula if possible please and retain the leading zero as currently I am losing the leading zero in column Y

Any assistance would be greatly appreciated

cheers

Paul

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you want?

=REPLACE(SUBSTITUTE(D2,"-",""),6,0," ")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try

=REPLACE(SUBSTITUTE(D2,"-",""),6,0," ")

I won't mention Saturday.

 

[paulsolar]

=REPLACE(SUBSTITUTE(D2,"-",""),6,0," ")

worked brilliantly thanks, i was struggling with this for half an hour

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Don't forget to update your version details though.

 

[paulsolar]

hat version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try

=REPLACE(SUBSTITUTE(D2,"-",""),6,0," ")

I won't mention Saturday.

I wouldnt either fluff

 

[JvdV]

Alternatively try:

=REPLACE(A1,5,2,MID(A1,6,1)&" ")