2

I'm running Excel for Mac Version 15.14, OSX Yosemite 10.10.5, and using an Office 365 subscription.

I have multiple cells in a spreadsheet that are in the wrong phone number format (long story). The phone numbers in the spreadsheet are currently in this format 12-345-67890, instead of 123-456-7890. I have tried using a custom number format (###-###-####), the Special > Phone Number format, and the Custom format they have for phone numbers ([<=9999999]###-####;(###) ###-####). None of them change anything.

Any ideas on how to fix this? Thanks in advance.

Improve this question
1
  • 1
    If it is stored as 10 digits, you can format the digits to look however you want. If it is stored as text, you are looking at a string and you can't change that via formatting. You would need to use string functions to either change the string to what you want, or extract the digits and then format them.
    – fixer1234
    Dec 30, 2015 at 16:30

2 Answers 2

Reset to default
2

Thanks to @fixer1234. I finally got it working using text functions as he suggested. This worked for me (indented for clarity):

=CONCATENATE(
   LEFT(SUBSTITUTE(A1, "-", ""), 3),
   "-",
   MID(SUBSTITUTE(A1, "-", ""), 4, 3),
   "-",
   MID(SUBSTITUTE(A1, "-", ""), 7, 4))
Improve this answer
0

Might I also suggest:

=--SUBSTITUTE(A1,"-","")

and format the cell as a phone number.

Or, if you want the result as a text string:

=TEXT(--SUBSTITUTE(A1,"-",""),"[<=9999999]###-####;(###) ###-####")

or

=TEXT(--SUBSTITUTE(A1,"-",""),"[<=9999999]###-####;###-###-####")

Explanation:

  • SUBSTITUTE removes all of the hyphens in a single step
  • The double unary -- converts the value to a string
  • The TEXT function then formats the value as a phone number
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.