Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Mobile Phones Numbers

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

When entering mobile phone numbers in excel the leading zero will not display
in the cell.

View Answers     

Similar Excel Tutorials

Make Negative Numbers Positive in Excel and Vice Versa
I will show you a few ways to change negative numbers to positive numbers and back again in Excel. Don't forget to ...
Convert Numbers Stored as Text to Numbers in Excel
I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situation often happens when you ...
Keep Leading Zeros in Numbers in Excel - 2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...
Excel Formatting for Zip Codes and Phone Numbers
Display numbers in Excel as zip/postal codes and phone numbers in order to make them easier to read and retain any ...

Helpful Excel Macros

Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Generate a Non-Repeating List of Random Numbers in Excel - UDF
- Generate a series of non-repeating random numbers in Excel with this UDF (user defined function). This is a great funct
Delete Only the Text from Cells
- This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cell
Format Cells as Text in Excel
- This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to cells
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
- This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof

Similar Topics

I have 4 columns of telephone numbers, both land line and mobile numbers mixed in together.

The mobile numbers generally start with 077 or 078 or 079 however smetimes excel seems to ignor the leading zero and make them 77----.

What I would like to do is find a way of flagging only the mobile numbers not the land line numbers and also fixing the leading zero problem where it is sometimes dropped.

I cannot post an example here as it has personal data etc.

The reason for doing this is to do an SMS mailshot to our customer but I only want to SMS mobile numbers, not landlines.

Any ideas?

Regards BVG

Ok, i used Sumif and Countif for a spreadsheet, but i need another column and need something like 'labelif' if it exists.

I have a phone bill with all calls made from our voip phones
Then i have a list of all of our mobile numbers
What i want to do is to put a coloumn next to each dialled call on our phone sheet saying 'us' or 'not us' if we dialled one of our own mobile numbers.
As the sheet has 11036 phone calls i really want a formula for it!!

Please help if you can!!! =)

Hi there

I have a situation where I am combining an existing inventory of mobile phone numbers (and associated info) with another spreadsheet of mobile numbers (and associated info) that has been extracted from a database of filled out online forms. I have come across, via a few manual searhes, a situation where a user has made slight mistake e.g. two digits mixed up, with the number.

Is there a way you can compare the two numbers to see how different they are, in order to ascertain whether that person has a new/different mobile number or has just made a small eror in entering their number into the form?

I have the mob numbers in number format as I have already been using vlookup, sumif etc functions on them.

Kind regards


I need to extract all mobile numbers from a range in excel for a text marketing campaign. Essentially all mobile (cell phone) numbers will start with the first two digits '07'. would like a marker or a true or fale entry in the next cell to highlight these mobiles.



P.S all phone numbers are in range AM:AM and I need to highlight the mobiles in column AO.

Example Data

NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55 151, 789, 545, 352, NLD Mobile LYCATL OP NLD Mobile LYCATL OP 55 654, 789

I need to convert this to



I have an export from a database that is personal details about some people, name, address, home phone number, mobile phone number and so on. The phone number fields are one to many, on the basis that one person can have multiple phone numbers. This is Represented by duplicating rows in the spreadsheet with all details except the phone number common to both rows.

Is there a way to manipulate this sheet so that instead of multiple rows I can get to phone number (1), phone number (2) etc. I guess an added complication is that one person may have multiple home phones and multiple mobile phones so the number of duplicate rows can grow.

Or is there a different way of making the data behave that anyone uses?

Any help would be appreciated

I wish to set up my phone numbers in a workbook. Many of these numbers start with zero, and I wish to display these leading zeros, but without using the apostrophe at the beginning, because this converts the information to a text string.

I am using this spreadshet as an input to my mobile phone SIM card, hence the numbers must be integers, not string text.

I would appreciate any help and suggestions

I wonder if someone can help me...

I have two columns A and B.
Column A contains a list of contacted/used Mobile/Cell Phone numbers.
Column B contains a list of new Mobile/Cell Phone numbers.
Mobile/Cell Phone numbers should be in the format of 11 digits (e.g 07879456789).
However, numbers can arrive in the spreadsheet in Column B with spaces or hyphens (e.g 07879 234567 or 078 796 54545 or 07879-876765).

Once all new numbers have been added to column B, then I am trying to get the following to happen at the press of a button :-

1. All numbers in both columns to have spaces and hypens deleted.
2. All numbers that are duplicated in Column B to be deleted (leaving just one instance of the number).
3. All numbers in Column B that are a duplicate of any number in Column A to be deleted (from Column B).

Hi! Thanks so much for your help! I'm new to this.

The following data is in column A:
Mobile 19171234854
Work 19171234854

Mobile 9171234854
Home 2121234567

Adrian Simpson
Mobile 19171234854

I would like to move the data to rows with the appropriate headers:
Name Mobile Work Home
Adam 19171234854 19171234854
Adele 9171234854 2121234567
Adrian Simpson 19171234854

As you can see, some of the phone numbers start with 1 and some don't. The listings may or may not include mobile, work, and home phones.

Thanks again for your help!

Hi. Hoping someone can advise me with this as I am not an expert in Excel.
My roblem is that I have a number of colums which one of the is for phone numbers, but when I try to add a mobile number, it wont let me put a 0 at the start of the number. Once I click out of the cell, the 0 just vanishes.
What do I have to do so that it will accept zero's.

Thanks everyone for your help.


I am trying to extract the data (via pivot table, if there is a better way happy to be told) whereby my data is a series of sales receipts with their sold items.

one receipt might be for a mobile phone
one receipt might be for a mobile phone and an accessory
one receipt might be for a mobile phone and internet connection

What am I trying to get? I want to know what products have been crossed sold against any mobile phone or an accessory with a mobile connection (on the same sales receipt number)

I am really unsure on how I would do this, I have included some data to see what can be done

Thanks in advance!!



I have a workbook with 2 worksheets (customer details & mobile devices). The mobile devices worksheet contains the columns: first name, last name, mobile phone 1, mobile phone 2, blackberry 1, blackberry 2, etc. The customer details worksheet contains the columns: first name, last name, title, device, phone number. The device column has a data validation list for the mobile devices (mobile phone 1, mobile phone 2, blackberry 1, etc). I am trying to work out how to create a vba script that does a vlookup for the user's name and based on what is selected from the validation list it then displays the correct phone number using the "mobile devices" worksheet data into the phone number cell in the "customer details" worksheet.

Function CalcVal(pVal As String) As Long
Dim MobileDetail As Range
Set MobileDetail = Worksheets("Mobile Devices").Range("B2:K199")
If pVal = "Mobile 1" Then
CalcVal = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, -4).Value, MobileDetail, 2, False)
End If
End Function

I have tried several things but I get a #value! error all the time. I'd appreciate any assistance.


I am putting together a master sheet of 1000s of contacts for colleagues taken from various sources (outlook contact download, linked in download, inputted business cards etc). I have noticed that everyone has their own way of using outlook and the data (normally addresses and phone numbers) appear in different columns and formats. I have used the function concatenate to add zeros back to numbers if i have had to use .csv as the download output, but I have the issue of mobile numbers in the wrong columns (i.e. someone has put a mobile number as the business phone). Is there a way of creating a new column and then applying a logic formula of some kind saying if a number starts with 07 then it appears in the new column, but if it doesnt then to ignore it therefore separating the mobile numbers so I can add to the correct column?


I'm trying to automate my works phone bill that I receive as an excel spread sheet - basically there is a row for each call (date, time, duration, cost, number, destination).

I would like to highlight all rows with a destination of "Non T-Mobile Number" and "Text Messaging" - as these are call's I have to pay for.

Ideally I'd like to create a seperate sheet with allowed destination types such as work collegues with with Non T-mobile phones i.e allowed numbers from other networks.

Any help for anyone would be great...



I have a simple spreadsheet which lists phone numbers and addresses. I'd like to be able to convert the phone numbers to an international dialling code in each instance. The problem is that the phone numbers are all entered in differing formats.

In Australia, the phone formats are as follows:

Country = 61
Area code - varies according to which of the eight states or territories it belongs, but range from 01 to 09. When dialling internationally, the leading 0 is dropped.
Landline phone = eight digits.
Mobile (cell) phone = ten digits all starting with 04. Again, the leading 0 is removed for international dialling.

I have already removed all leading 0s and any other symbols like brackets or dashes.

The phone numbers are all contained in columns B and C. I'd like to do a 'look up' of columns E (State) and G (Country) and base the conversion upon that information.

What I'd like to do is to convert them ALL to 11 digit numbers for international dialling. For example:
Where column E = VIC, column B may contain the number set as a 312345678 or just 12345678 (the initial 3 is used to denote Victoria or VIC).
In this case, I need to convert the number to 61312345678. It need to recognise that VIC's are code is '3' and add it if it's missing. It also needs to add '61' for the country at the beginning, if it's not already there.

I can provide a sample of the various types of numbers which I'm encountering. Unfortunately, it appears that I cannot attach this sample though.

I'm hoping that someone can help me by creating a macro to sort and convert these numbers? I'd like a macro (if possible) because I'd like to use it with other spreadsheets yet to be created. You may, of course, know of a better way of doing this? Hoping that all makes sense! Can you help please?

I have a column of cells that contain phone numbers and descriptions of those numbers. Example: in A1 I have 555-555-5555 Business, in A2 I have 777-777-7777 Office, in A3 I have 888-888-8888 Mike Off, in A4 I have 999-999-9999 Mike Cell, etc.. The text is fairly inconsistent and for each category there are a number of ways that the text is written in.

My goal is to separate the phone numbers and apply them into the appropriate column based on the descriptor from the cell in column A. My categories are Business in column B, Home in column C, Mobile in column D, and Fax in column E.

Essentially I'd run a formula in Column B that searches the respective cell in column A for any of the following terms -- Business, Bus, Biz, B, Direct, Office, Off, Work, Wk, W. If there is a match, then the phone number appears in the cell in column B. If not, then blank "".

For Column C Home, my search string should be Home, Hm, House.

For Column D Mobile, the search string should be Mobile, Mob, Cell, Car.

For Column E Fax, the search string should look for Fax, F.

Hopefully none of these are case sensitive in Excel's search string.

Using Excel for Mac 2008 so no VBA, no macros unfortunately. Thanks for any help.

Hi there,

Bit of a complex one here ..I think !
But here's an example of what i have anyway

93 Afghanistan Afghanistan 17.22 17.22 17.22
9370 Afghanistan-Mobile Afghanistan-Mobile 16.40 16.40 16.40
9371 Afghanistan-Mobile Albania 6.04 6.04 6.04
9372 Afghanistan-Mobile Albania-Mobile 11.30 11.30 11.30
9379 Afghanistan-Mobile Albania-Tirana 3.47 3.47

Basically what i need it to do , is check column B against column C and
if it has an entry in column C then to copy the values in D,E and F and
paste them into empty columns on the same row.
So, in effect what we would be left with is

93 Afghanistan Afghanistan 17.22 17.22 17.22
9370 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4
9371 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4
9372 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4
9379 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4

Is this even possible to do as i'm having a lot of issues trying to
implement it correctly myself ?
Also to note is that there is a large number of entries in column B and
a smaller number in column C.
It doesnt really matter what output column C is either, as all that
matters is to have the correct number of lines with the right output in
columns G,H and I or where ever.

Many thanks in advance.

This one is driving me up the wall - hopefully its an easy piece of code to resolve..

I have two phone bills and they are presented in different formats. I have each bill on two separate sheets for the appropriate number. All the number mobile 1 calls are listed for example


Mobile 2's bill is listed as:


what i've done so far is run through all of mobile 2's bill with the following:


Selection.NumberFormat = "00000000000"

This displays the value in the right format in the cell, but my problem is I have another piece of code which compares numbers called in both bills and generates a list of numbers both mobiles call, i.e. numbers which appear in both lists.

The issue is that its not comparing this data correctly as the formats are technically different and the value in mobile 2's bill is still 7771234567. Is there a piece of code i can add in to whizz through an active selection and change the actual value to 07771234567, i.e. put the 0 at the start?

many thanks


I am looking for a formula to count the number of valid mobile phone numbers in a list and the number of valid email address in another list, 2 different formulas.

The mobile phone number formula would count the number of indervidual mobile phone numbers in a list that contain 077 and 078 and 079.

The email formula would count the number of indervidual email addresses in a list that contain com or co dot uk

Both lists may contain random junk text, like in the email list some one may have entered words like 'no email address' etc

Thanks in advanced to all helpers.


Hi all, 1st post on this board, it's a query relating to formatting data.

I get sent data in excel format, which contains mobile phone numbers. The data is sent to us in this format 7777666555. Im based in the UK so this mobile is 07777666555....basically the supplier leaves off the zero at the start of every number. I need to get the zero back at the front of all these numbers, is there anyway I can do this as I am sent 2500 lines a week and I dont fancy manually editing them!

Many thanks for reading

I would like to store many phone numbers in MS Excel Mobile version 6.0 and be able to click or shift+click on them to dial them from Excel. The reasons are several, but wessentially bering able to store and collate sets of numbers differently from use in my MW Mobile OS system's Contact list. Hence two issues come up at least:

1) Being able to format phone numbers in a recognizable format. Just typing (203) 824-4969 as a number does not create at this time a truly formatted, dialable number. Also, having recognizable formats for the array of international numbers/countries.

2) Being able to dial these via the PDA. So I assume that means that a dialer component must be able to accept the cell's selected number and process it properly through the PDA's telephone component.

Help so appreciated. This is NOT for any sort of large scale commercial process, just for me; so, this solution cannot be a million dollar baby.

Chuck Cohen

Cell: (203) 824-4969

How do I stop mobile numbers losing 1st 0 digit when I press enter?

Hi everybody,

I've got quite a challenge. Every month I receive a huge file from the mobile phone company with the call details of every employee. Something like:

Phone number / Number dialed / Call date / Duration / Cost

I have to send every employee the details of his/her calls.

I'd like to have Excel do the following:
-Filter the range to find unique cell phone numbers and group them
-Create a total for the cost column (for each mobile phone number/group)
-Put this filter in an e-mail
-Send it to the e-mail of the phone number's owner

This would really be a time-saver.

Of course, I would have to have a worksheet with the mobile phone number and the owner's e-mail where the macro would find the corresponding e-mails.

I know there are tools to do this out there, namely

but I can't insert a subtotal on the filtered range before collating it on the e-mail.

Can somebody please help?

Thanks a lot,


I have data Like Below, Its a Contact No. of the Customer, I have 21000 Contact Number including Mobile Numbers.


E 1 Phone Number 2 1322711122 3 9006265287 4 919029834054 5 9143051407 6 919743592860 7 1147072589 8 919327538823 9 9333102861 10 919810332270 11 222365987 12 9399918762 13 9424652200 14 919472253566 15 9570085897 16 9570187279 17 9580299340 18 9658757751 19 9658909642 20 9660847468 21 9713495479

Excel tables to the web >>" target="_blank"> Excel Jeanie HTML 4

Some has Landline Number Some have Mobile No.

Landline Number have also STD Code of there Cities

Like In Delhi

1122470589, 011 Indicate the STD Code of Delhi Followed by Landline Number.

In Mobile Some Numeber is Starting with 91

Like 919810332270

Now I want to Copy Only Mobile Numbers In another Column After Extraction of 91 from Some of the Mobile No.

Expected Result Should be:


G 1 Phone Number 2 9006265287 3 9029834054 4 9143051407 5 9333102861 6 9810332270 7 9743592860 8 9327538823 9 9399918762 10 9424652200 11 9472253566 12 9570085897 13 9570187279 14 9580299340 15 9658757751 16 9658909642 17 9660847468 18 9713495479

Excel tables to the web >>" target="_blank"> Excel Jeanie HTML 4


What is the Simple way to do this, Need Forumal if Possible

Thanks in Advance

Hi All

I have an excel file that contains 39,000 rows of data and looks like this:

Mobile Number | IMEI | ID Number | Sale Channel

There are multiple Mobile NUmbers, ID NUmbers and Sale Channels for each IMEI.

For each IMEI i want to make a table which lists how many mobile numbers, ID Numbers and Sales channels are there liek this:

IMEI 1 | 11 | 11 | 3

same IMEI are listed multiple times (in rows) for different mobile numebrs, ID numebrs, etc.

Teh best i can do is filter IMEIs together and then count teh rest of the data for each IMEI. But for 39 rows, it seems impossible.

Can someone guide me how i can do this using formulas. I dont know much about formulas so if you give a solution can you please make it simple and easy?

Thanks a lot people!