Formatting for telephone numbers

[SimonHughes]

Hello, I would like to reformat telephone numbers to have spaces as per this example: 01299289955 - 0129 928 9955 can anyone help me on this? I have actually solved the problem by using a combination of Left/Right and Concatenate formula but wondered if there is a quicker way. Thanks in advance

I am using MS365

 

[SanjayGMusafir]

01299289955 - 0129 928 9955

Use Custom Format ### ### ####

 

[SimonHughes]

Use Custom Format ### ### ####

That's not working. The number first needs to have a zero added to the front (zero formatted as text) and this seems to prevent using the customer format to it??? I start with (say) 1912567846 then concatenate this with a leading zero '0 to give me 01912567846 then copy/paste as value then apply custom formatting but it does not apply?

 

[SanjayGMusafir]

That's not working. The number first needs to have a zero added to the front (zero formatted as text) and this seems to prevent using the customer format to it??? I start with (say) 1912567846 then concatenate this with a leading zero '0 to give me 01912567846 then copy/paste as value then apply custom formatting but it does not apply?

It's a different question from your original question....

as per this example: 01299289955 - 0129 928 9955

 

[kevin9999]

Try Custom format: 0000 000 0000

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	1299289955	0129 928 9955
Data

Cell Formulas
Range	Formula
B2	B2	=TEXT(A2,"0000 000 0000")

 

[SimonHughes]

It's a different question from your original question....

Yes it is - my apologies

 

[SimonHughes]

How about

Fluff.xlsm
	A	B
1
2	1299289955	0129 928 9955
Data

Cell Formulas
Range	Formula
B2	B2	=TEXT(A2,"0000 000 0000")

Hi Fluff, with a bemused look on his face, the weary spreadsheet idiot said 'like magic, the solution proved to be the solution' Many thanks indeed

 

[Fluff]

Glad we could help & thanks for the feedback.