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:
- 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.
- 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.
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:
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:
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:
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:
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:
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!