Monday, July 7, 2014

How To Add First Zero to Nigerian Phone Numbers At Once With Excel

There are some bulk sms sites that will not process your bulk text messages if your Nigerian phone numbers do not start with the first "0" or "234". This might be frustrating if you need to send a message to thousands of phone numbers without the leading zero.

Anyway, don't fret. You can add the leading zero to all your numbers in excel at once using the Excel TEXT function. The Text function will add zero in front of numbers, and make the new numbers with leading zeroes in certain length.

leading zero excel

The first column of this example spreadsheet contains a list of Nigerian phone numbers without the first zeroes.

The second column contains the reformatted phone numbers with the following formula: =TEXT(B2,"00000000000")

For you to add the leading zeroes to the numbers in the first column, follow me through the steps below using my sample spreadsheet as guide:

==>  Enter the formula =TEXT(B2,"00000000000") in a blank cell which is adjacent to the first data cell.

Change the B2 in the formula to that of the first cell in your spreadsheet. C2 is the adjacent cell to B2 in my sample spreadsheet.

==> Then press Enter key

==> Drag the fill handle of C2 across the range that you want to fill. The screenshot above is what the fill handle looks like.

You should see that all the numbers in B2:B6 have been copied and pasted to C2:C6 with leading zeroes. And each numbers contains 11 digits.

That's all.

No comments:

Post a Comment