How do I add "1" before phone numbers on excel

Copper Contributor

The phone number that I exported to excel sheet do not have (1) in front of them, so how do add that number?


9 Replies



The phone number that I exported to excel sheet do not have (1) in front of them, so how do add that number?


That very much depends on how your phone numbers are stored. Are they stored as numbers with the special formatting of a phone number? Or are they stored as text (numbers, yes, but stored as text)?


One way to tell is if you make the column in which they appear wide enough, phone numbers stored as numbers will line up to the right; phone numbers stored as text line up on the left. See  below


If it's text (the second example above), then you write a formula in an adjacent cell that just concatenates "1 " with the phone number: ="1 "&A4


If it's a number, formatted as a phone number, you can use the number formatting dialog box


and select "More Number Formats" from the bottom of that range of choices, then "Custom"


And create your own custom format and apply it to the cells containing the phone numbers without the 1



See the attached sample spreadsheet


@mathetes So if I wanted to change the number from (352) 123-4567  to 1(352)123-4567, then I type the following: "1"(352)123-4567? I tried that and it did not work



Sorry. I guess I wasn't clear enough. First, are your phone numbers saved as text or as numbers? That's the first thing we have to determine.


The adding of a "1" in front of the phone number by typing it only applies if the phone numbers themselves are saved as text values, and even then it takes place in a formula, not just by typing it in.


I gave you one way to make that determination of whether it's text or numeric data.


IF they are text, THEN you need a formula that concatenates (combines) the text "1" or "1 " (one with a space following it) with the textual phone number. That would be in a separate cell, generally the adjacent cell, and the formula would be ="1 "&A4   (where A4 is the cell with the original--needing to be changed--phone number. Here's a link to a helpful reference to concatenation, which can be accomplished via several different routes; the one I gave is the simplest in many ways.


If they're numeric, then it's by means of special formatting.


Is it possible for you to post a copy of a small sample of the file you're working with? Put it on OneDrive or GoogleDrive with a link here that grants access. Make it as anonymous as possible (e.g., no real names; and ideally modify  the phone numbers ever so slightly to false numbers)



By the way, did you look at the sample file I attached in my first reply?


I demonstrated both possible solutions. That file, plus the explanations in that first answer, is fairly complete. Either part by itself, though, is incomplete,

Thank you so much for your help. Maybe you are clear, but I am not versed in this at all. Here is the link. I hope it works!AgiQOzOlQ0mKhOpTg4gyDOxDyBo_bg?e=cAlEb7



Maybe you are clear, but I am not versed in this at all.


That much IS clear. You're to be commended for recognizing that fact and coming for help.


Here is the link. I hope it works


It did work. You should be able to go back to that file in OneDrive and open it to see what I did.


Now: IF that sample is in fact the same as your actual, those phone numbers are stored as text. So I used the simple concatenation formula to add the 1 in front. That formula is in the cell immediately to the right of your sample data. As such it could be copied and pasted to your actual.

I am sorry , can you clarify?



Did you go back to the OneDrive sheet? I gave you the solution there.


Either way, what are you asking me to clarify?


Never mind. I just saw it Thank you very much for your help