Phone Numbers not all aligning to the right

[SueWalt]

I have a list of phone numbers in my excel sheet. I formatted them using Special and Phone Number to get (250)999-2222 Format. Weirdly some align left and some right? (Not sure this has any bearing but cells with no phone number are marked None. )Please advise. Thank you in advance.

 

[6StringJazzer]

For the ones that align left, what is the raw data and what is the displayed format? If it aligns left it may be text rather than numeric (the format requires numeric data). The formatting will not show "None" so I'm not sure how that's happening. Please provide sample data here either using XL2BB or a straight Paste into a post.

 

[SueWalt]

It's Formatted as Special and Phone Number. The raw data is just the number typed in. The None in is just what I have typed in when I don't have a phone number for them.

(250)869-5569
(250)878-9842
(778) 363-7725​
None
(780)893-6270
(778) 363-9995
(250) 938-0629​
(778)215-8424
None
(250)550-0471
778)549-4070
(250)869-6161
(250)868-1541
(604) 477-3088
(778) 861-1521​
(250) 488-0759​
None
(250)212-3813
None
(250)808-2399
(250)300-5170
(905)999-2891
(250)859-0935
(778)986-6398

 

[alansidman]

you would be better served by us if you reposted your data using XL2BB. Your current presentation when copied and pasted into an excel sheet will appear as Text and cannot be determined as to the causation.

 

[Dave Patton]

T202401a.xlsm
	B	C
1
2	Initial	Custom number format
3	2508695569	(250) 869-5569
4	2508789842	(250) 878-9842
5	7783637725	(778) 363-7725
6
1f

Cell Formulas
Range	Formula
C3:C5	C3	=B3

Column C uses custom number format [<=9999999]###-####;(###) ###-####

 

[Peter_SSs]

It's Formatted as Special and Phone Number. The raw data is just the number typed in.

Welcome to the MrExcel board!
It is odd then that some of the results have space characters in them and some don't.

If you individually select any of the left-aligned (or right-aligned) cells, do any of them show as formatted left (or right) aligned on the alignment section of the Home ribbon tab?

 

[6StringJazzer]

All of your left-aligned values (except one) are missing the space after the ")"
(250)_488-0759
like this
(778)215-8424
I doubt that is a coincidence.

This makes it look like that is a text string, because the format you are applying will not give you that result for a number.

For a left-aligned value, what do you see in the formula bar for the underlying value of the cell?

We need to see your actual data and format settings. This would go much faster if you had some way to share your file, such as with Google Docs, Dropbox, OneDrive, etc.

 

[SueWalt]

Thanks so much for all the replies. I have it figured out now.

 

[6StringJazzer]

Could you please share your solution with us?

 

[SueWalt]

I just formatted them to align right.