Email:      Pass:    Pass?


Advertisements


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.



Similar Excel Video Tutorials

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


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




NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55151 NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55789 NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55545 NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55352 NLD Mobile LYCATL OP NLD Mobile LYCATL OP 66789 NLD Mobile LYCATL OP NLD Mobile LYCATL OP 66789 OR NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55 151 NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55 789 NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55 545 NLD Mobile KPNMOB OOP NLD Mobile KPNMOB OOP 55 352 NLD Mobile LYCATL OP NLD Mobile LYCATL OP 66 789 NLD Mobile LYCATL OP NLD Mobile LYCATL OP 66 789


I am desperate for some help on this one. The problem is now accepting numbers starting with zero but it does involve formatting/ masks.

I have a sheet with many mobile/ cell phone numbers:
07771123456 for example
But I need to change this format to drop the "0" and add the country code "44", so need to automate this somehow with a mask/macro.

07771123456 needs to change to 447771123456 and there is thousand of these so I cant face any more hours of doing it manually, there is a way isnt there? please for my sanity

I use office 2007.


Hello

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.

Thanks

Paul

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


Hi,

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
Andyy


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


Hello all,

I have an issue on my new laptop that runs Excel 2007 on windows vista.

I have downloaded a list of phone call data from the orange website that comes in a large excel document.

One of the columns is "dialled Number" which shows all the calls made to other numbers and can contain both landline numbers and mobile phone numbers.

Landline numbers show as they should i.e. 1925810886 , and mobile phone numbers i.e. 7794049589 . The issue i'm having arrises with all mobile phone numbers that have the international prefix before them i.e 447892537261, in my excel sheet numbers such as these are simply displayed as 447892000000.

It seems to not show the last six didgits of the number and instead displays them as six zeros instead. I have my old laptop upstairs which runs excel 2003 i believe and out of interest i downloaded the same data onto that and it didnt have this problem... All the mobile numbers are listed properly on that and do not have the six zeros in place of the correct numbers.

Hopefully this is something really simple as i cant use my old laptop to do the work as it is unreliable to say the least!

Please can anyone suggest what might be the problem?

Many Thanks, Joel.


Hi,
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 guys/girls, new here. I really need your help.

I know a lot of stuff in Excel, but have never used macros, excep SUM function.
So i need your help on this:

Soon, a mobile provider, on whom we shall make contract with, will send us a list of mobile phones, and how much users used them in terms of money. And it will be in excel format.

Examp:
......................A...................................B
1 .......0134367890(cell number) 134(money)

I wish to create an excel file, which will contain names of users and their mobile numbers, and all I want to do is, that this excel file, which I will create, can read external excel file(provided by mobile operator) where my excel file reads pricing according to mobile number provided, and puts them in certain places, where in the end it will calculate pricing for every user.

Thnx.


The Webalo Mobile Dashboard allows you to easily deliver excel
spreadsheets that are very easy to read and navigate to mobile devices
- including BlackBerry, PocketPC, Treo (700w windows os), and smart
phones.

We are releasing a new service, the Webalo Mobile Dashboard, and would
like to invite you to be a part of our beta program.

We host WebEx demonstrations every Tuesday and Thursday. After the
demonstration, you'll be able to use the Webalo Mobile Dashboard on
your own.

You can sign up at http://md.webalo.com

You can contact me at sgordon@webalo.com.

Scott




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

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

Adele
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.
Cheers


Hi

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.

Thanks


Hi All.

I have an excel spreadsheet that has been exported from a database program. One of the columns contains a mobile phone number. The problem is that everybody enters this mobile phone number differently. For example it could be 0407-123456, or 0407-123-456, or 0407 123 456 etc.. I need the mobile phone number in the format 0407123456 with no spaces or dashes etc...

Can anybody help??

Thanks in advance


Hi

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

Mike


Hi there,

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

A B C
D E F
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
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.
Declan




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.


G'day!

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?





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

07771234567
07771234568
etc

Mobile 2's bill is listed as:

7771234567
7771234568
etc

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

Code:

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

col


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 ...........@..........dot com or .............@............dot 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.

BVG


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 ...........@..........dot com or .............@............dot 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.

BVG



Hello,

I'm Scott Gordon with Webalo. We are releasing a new service, the Webalo
Mobile

Dashboard, and would like to invite you to be a part of our beta program.

The Webalo Mobile Dashboard allows you to easily deliver business
intelligence reports

including excel spreadsheets with data such as key performance indicators,
scorecards

and dashboards, to mobile devices - including BlackBerry, PocketPC, Treo
(700w windows

os), and smart phones.

We're hosting short online WebEx demos every Tuesday and Thursday. You can
sign up at

http://md.webalo.com . We're very excited to hear your thoughts and
feedback on our new

technology. After the demo you'll be given your own trial Mobile Dashboard
account.

You can contact me at sgordon@webalo.com.

Scott





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