Adding specific Prefix to All cells or Specific cells in Excel – 3 Methods Explained

3
8449

A dear friend of mine contacted me early in the morning and the length of his message was evident that he has tried his best to get his head round the problem but in vain.

The reason was an excel file with over 25,000 contact numbers with or without area codes before actual contact number. So, the actual problem was not the sheer volume of data. The real problem was that some of the numbers had area codes before them and some of them not. Now adding codes manually where its missing could have been possible if it was few tens or hundreds but its no way practical for twenty-five thousand rows of data.

I will be explaining some of the possible solutions:

  1. firstly, if all of contact numbers were without area codes. This is fairly easy task. I will discuss two methods to deal with this problem. One solution involves the use of helper column and the CONCATENATE function via symbol approach. Second solution is using Excel’s Custom Number formatting.
  2. And then I will explain one easy solution to add area codes to numbers where it is missing. This involves the use of helper columns and mix of functions. But it is also not really a difficult task.
For the purpose of this tutorial and in order to understand how to best apply the techniques discussed in this article download Sample Excel tutorial file and follow the given instructions

1 – Adding prefixes to all cells

Method 1 – Helper columns and CONCATENATE

Step 1: Download the tutorial file and make sure that “No code” worksheet is active. This worksheet contains contact numbers from A2 to A1000 and none of them has area code.

Step 2: In column B’s cell B1 put a heading “Area Code” and press Enter. Now cell B2 will be active.

Step 3: Move your cursor to Name box and type B1000. Instead of pressing Enter key press Shift+Enter combination. This will select all the cells including B2 to B1000.

Step 4: Hit F2 key while having the cells still selected. This will bring you to cell B2 in edit mode. Type 042 and again instead of pressing Enter key press Ctrl+Enter. This will quickly insert 042 in all the cells and save you the hassle of dragging the fill handle down to cell B1000 or even double clicking it ūüôā

Fixing “vanishing” ZERO problem

Right when you were thinking that you are half way through with this problem of having area codes before contact numbers, you are struck with another problem i.e. Excel is automatically taking out starting zero digit from the area code.

Now before you start seriously cursing Excel’s developers let me tell you that Excel is doing fine to the extent of embedded intelligence. Excel cannot differentiate between a number and an area code.¬†In mathematics 042 = 42 so Excel automatically takes out initial zero or prefix zero as its there for no reason and ultimately saves file space.

But in our case its not really a number and we really need this ZERO. To tame Excel for this follow these steps:

Step 1: If the cells B2-B1000 are not still selected then reselect them by making B2 active by hitting Ctrl+Shift+Down Arrow key

Step 2: Hit Ctrl+1 shortcut or go to Home tab > Number group > from the drop down select More Number Formats. This will bring the Number format dialogue box up.

Step 3: From the left hand side select custom and in “Type” input bar punch three zeros. This will effectively make 42 as 042.

In step 3 you are asking Excel to report at least three digits even if the original content is of less than three digits. Therefore, Excel is forced to add preceding zero. This got our job done as well.

However, if showing correct area codes is not part of the job then you can skip this correction altogether as we are going to repeat this again once we join the area code and contact number together.

Binding Area code and Contact number together

Step 1:¬†In column C’s cell C1 type heading “With area code” and in cell C2 put this formula and press Enter key:

=B2&A2

Step 2: To populate all the cells with area code+contact number double click the fill handle of cell C2.

Here we ran into similar problem as we had with area codes. The numbers are not starting with necessary ZERO as Excel has taken them out. So can we repeat the same procedure here as well? Lets see.

Select the cells and hit Ctrl+1. From the window that opens select custom and in the type input field start punching zeros. We must get a preceding zero by having 11 zeros. But even if you put 21 zeros its not affecting the numbers at all.

Is Excel out of its pants again? Well, not really!

The reason why you are not having preceding zero this time is because when we attached area code with contact numbers via CONCATENATE function “&” the end result was no longer a number as it was converted to TEXT and on text, number formatting does not work.

Helping Excel to remember the number

There are many ways to do it but the easiest is modifying the existing formula in cell C2 a little as follows:

=(B2&A2)+0

And BINGO! Now its a number. The best evidence is that the output is right aligned automatically. Now if you apply number format it will work flawlessly.

But hold your horses and if you have already decided to put 11 zeros in custom format options. There is a better solution. Follow along.

Formatting the right way

Select the column C’s data and hit Ctrl+1 to bring the custom format dialogue up. Select the custom option from left and put the following in the type input bar and click OK:

000-000-0000

This will not only put a zero at the start but also make it look like a contact number.

Method 2 – Adding area code via Custom formatting

If you only want the numbers to appear the right way then you can yourself a hassle of going through all the above steps. And using custom format to have this done is the fastest and the easiest way to do it.

Step 1: Make worksheet named “Custom” active and you will find lots of contact numbers here as well.

Step 2: Select all of the numbers by select the first number in cell A2 and pressing Ctrl+Shift+Down Arrow key.

Step 3: Hit Ctrl+1 to bring the number format dialogue box up. Select custom from left hand side and insert the following in type input bar:

"042"-000-0000

Click OK button and here you have it. All the numbers with area code appended before each of them.

2 – Adding specific prefix to specific cells in Excel

Our previous situation was rather easy as all of the numbers were without area codes. However, things get a little complex when some numbers have area codes and some don’t. To solve this problem we will have to tweak our formula a little more.

Step 1: Click worksheet tab named “Specific cells” to make it active.

Have a look at the data. You can see that numbers are formatted with dashes and that is the reason they are not really numbers and Excel is taking them as text but it will not affect us as we are not really going to use these contact numbers to perform any mathematical operations.

Step 2: Make cell A2 active and press Ctrl+Shift+Down Arrow Key to select all the cells. Or simply use to select the cells as they are not much.

Step 3: ¬†In cell B1 put a heading “Contact number with area code” and in cell B2 put the following formula and press Enter key:

=IF(LEFT(A2,4)<>"042-","042-"&A2,A2)

This formula is checking whether the first four letters or items in cell A2 are not equal to “042-” then put “042-” and then put the content of cell A2 after that. In other case i.e. if first four letters of cell A2 are equal to “042-” then simply insert the contents of cell A2. This way the numbers that do not have area code before them will get it and the ones that do have will not be affected.

Step 4: Double click the fill handle of cell B2 so that same formula is applied for the rest of the data.

And that is it!

3 COMMENTS

  1. Dear Hasaan
    Thank you so much for your reply. Much appreciated
    As for the information required Ill do my best to explain

    Most numbers are cellular numbers and in deed should be in the format 0020 followed by 10 other digits.
    When we do have combination of two or more numbers; these may be two or more cellular numbers
    or two or more numbers with one of them being a landline number in the format 00202 then 8 digits

    So, I might have in the column 0020XXXXXXXXXX ::: 00202XXXXXXXX ::: 0020XXXXXXXXXX

    I do not want to make it even more complicated but in quite a few of these 3000+ numbers I also have the numbers whose country code differs naturally from 0020, and also some landline numbers from other countries!!
    Is there any hope?!
    Thanks bro, really!

  2. Hello
    Thank you so much for this it has helped me a lot. I have spent almost a whole day trudging through my phonebook numbers and names (~4000) trying to clean them up and format them correctly.
    I still have a few problems if you could be so kind as to help me
    In the numbers cells for example I finally have just numbers in say the following format: 0020XXXXXXXXXX , where 0020 is the code. However I also have numbers within the same column in the following wrong format : 002020XXXXXXXXXX Is there a way to standardize them all to the first format? Also, and I hope I am not being too imposing by this.
    Since I copied the excel sheet from the google doc exported from gmail contacts, some of these cells in this same column have numbers in this format : 00200XXXXXXXXXX:::0XXXXXXXXXX
    Lastly, there are some landline numbers thus in the following formula: 0020XXXXXXXX Problem is sometimes these are also combined with those that have ::: in them.

    Could you please kindly help or direct me as to how to solve this problem?
    Thank you in advance

    • Hey Nasef,

      Thanks for inquiring. OK another data cleanup job. We first need to understand the difference between two things.
      1: do we have to modify data to show in proper format; OR if we are lucky
      2: we can format the data simply without modifying the data

      In your first case where codes are wrong it seems we will have to modify the data. For that you will have to help me find a pattern which is applicable to almost entire data it will get us close to what we want.

      As per your example it seems that all numbers are from same country/area. So what if we get the “x” numbers on the right and then add 0020?

      For example: 0020XXXXXXXXXX
      the ten digits at the right represented by x can be extracted and 0020 added to them as following:

      =”0020″&RIGHT(A13,10)

      This way we will get rid of wrong codes at the beginning and insert the correct ones.

      The second case require more information for me to assess the approach needed to solve it like is it always the case that second number is landline? as it seems its shorter than other numbers

      Regards

LEAVE A REPLY