Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 All Numbers in a List Positive in Excel
Take a list of numbers and make them all positive, regardless of whether the list contains a mixed set of positive ...
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 ...
Generate Random Numbers within a Range in Excel
How to generate random whole numbers (integers) that are between two numbers.  This allows you to set a minimum and ...
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 ...

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


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


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.


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


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


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


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?





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

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


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
Email: cacohendvm@gmail.com


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 http://www.rondebruin.nl/mail/folder3/row2.htm

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,
Regards
Miguel


Hi Team,

I have a file in which different fildes like:

Name
Address
Telephone Number
Mobile number

from which i want to extract mobile numbers.

Please help me with that.

Formula such as "Begins with", or =IF( / =AND(.


Thanking you in advance.


Good morning all,

I need a formula to help me count specfic phones numbers, i need to count what is a land line, mobile and a 0800 number the formula i have tried is Code:

=COUNTIF(H2:H10000,"*07*")


but this is picking up everything if it contains for example 07, i need a formula that will look for the first 2 numbers at the start of the telephone number only

Thanks in advance

DJ


I'm working with a sheet of contact details and very, very annoyingly, the column of phone numbers has some values messed up.

My numbers are generally 01XXXXXX , or 08XXXXXXX , or if 353XXXXXXXXX

Excel converts the 01XXXXX numbers to 1XXXXXX and the 353XXXXXXXXXX numbers it displays in some scientific notation.

I've tried changing the format of the column to both text (hoping that it would restore my leading zeros and special (mobile phone - Ireland), but that doesn't help.

In one of the cells where a number has been converted to scientific notation, the number is shown as 3.53XXXE+XX, but if I select the cell, the correct number is shown in the bar at the top of the sheet (dunno what it's called).

Some other threads here mentioned changing the fixed decimal, but that doesn't seem to have helped me.

What else can I try, please?


Hi guys I am trying to determine if a phone number has a certain prefix or not. I am using Windows XP with Excel 2003.

So I have a list of mobile numbers in one column and on another sheet a list of prefixes.

In Australia the Mobile number format is:

0400000000
0407000000
0408000000

Etc etc... So the Prefixes I am looking for are (With the "0" dropped off the front due to formatting):

400
407
408

An example of numbers could be:

400995729 (Should return "True")
411831823 (Should return "False")
419024711 (Should return "True")
408335463 (Should return "True")

The code I am using is...

=ISNUMBER(SEARCH(Formulas!$O$28,'First Service'!L6))

And I would like to try and get the wildcards working but I can't seem to find anything that covers this type of thing... Also I need to get it to check the range of prefixes between O28 and O44 but when I try anything like O28:O44 it give me an error and I am unsure how to fix it.

Essentially it is just the first 3 numbers that I want to match if possible.

I.e. " 408* " rather then " *408* " as it could return an incorrect result due to the same sequence of numbers in the mobile number (eg. 412 408 000)

Just wondering if anyone had an idea or feed back how to get it to work more accurately?


Hi all,

I'm working on a bit of code that will sort telephone numbers into the correct columns. I have three columns, Home Number (D), Work Number (E) and Mobile Number (F). The data I have is very messy and the numbers can be in any column, ie mobile numbers in home number column and vice versa.

The logic that I'm trying to use is, if the number starts with a 7 (all numbers have the preceeding zeros removed) then it is a mobile number and should go into column F. If it starts with a 1 or 2 then it should go into column D or E (put it in column D, or if that is already populated with a number begining 1 or 2 then put it in column E).

Also sometimes there can be more than one mobile number, in which case the second mobile number should go into the work number column (E).

This is the code I have so far but it doesn't seem to do anything (sorry I'm not sure how to display it properly!)

Dim MTN, BTN As String
Dim bCell, mCell As Range

lastrow = Range("A" & Rows.Count).End(xlUp).Row

Range("D2:D" & lastrow).Select
For Each cell In Selection
cell.Select
mCell = ActiveCell.Offset(columnoffset:=2, rowoffset:=0).Select
bCell = ActiveCell.Offset(columnoffset:=1, rowoffset:=0).Select
MTN = mCell.Value
BTN = bCell.Value
If Left(cell, 1) = "7" And MTN = "" Then
mCell = cell.Value
ElseIf Left(BTN, 1) = "7" And MTN = "" Then
MTN = BTN
End If
Next

Thanks for any help,

Jay


Hello Excel experts!

I have a worksheet containing names,job titles, email addresses, and contact numbers of project staff. The contact numbers are mainly uk mobile numbers (07973100000), some already in international format (+44 1234567890), some internal (706 4678), some foreign (+33 222 222 222), some wrong (+40788888888), some empty!

First, I'd like to store all valid numbers in international format. Uk mobile contacts are currently stored mainly with spaces, to prevent leading zeros from disappearing (079 100 0000). I'd like to remove spaces and replace any number beginning 07 with +447. So any strings, after whitespace + special characters are removed, beginning with 07 and 11 characters in length, will begin +447. These should be stored as text, I'd imagine, to ensure the format (leading +) is preserved. The xls file will also be used to create group contacts csv files for an online sms tool, hence the strictness.

I also need to get rid of any brackets, hyphens etc.

Then, I'd like to replace any numbers beginning with "00" with a "+", and remove any whitespace within the string.

Also, is there any easy way of extracting a list of anyone without either any contact number, and maybe one that's identified as being invalid ?

Know there's a lot to ask there, but I'd be very willing of any suggestions.

Cheers,

Jonny,


Hi guys,

I'm sure this sort of thing has been discussed so if appropriate please point me to a relevant tip or topic but I am going to put my case clearly as I really need to get some data formatting for a business card data merge with InDesign.

I have to ensure that all the data is formatted uniformly across the business and this includes separating all the numbers in the business cards with a single space and finally including the p/f/m identifier and outputting as csv...

The spreadsheet looks like this
Code:

Murray	Scott		Production Printing Specialist	Production Printing Systems	68 Hasler Road Osborne Park WA 6017	08 9347 2222	08 9242 4128	0417 223 171	scott_murray@canon.com.au	/pps	1800 334 346


The columns a
first name, surname, title, division, address, phone area code, phone1, phone 2, fax area code, fax 1, fax 2, mobile 1, mobile 2, mobile 3, email, url suffix, switch number

I have several problems the first is the tendency for any leading zeros to be dropped unless the data is saved only once as csv/txt - ie as soon as excel works with the data again the leading 0 gets dropped because the original formatting of the xlsx file is lost and the columns revert to standard number formatting. Therefore I have to be very careful that I export the data as a text file only once and not 'resave' it upon closing.

The next thing is to "build" the full fields that InDesign will need to populate the data. I need to output the data in the following format

first name, surname, title, division, address, phone, fax, mobile, email, url, switch

Now the problems occur when there is no fax and extra spaces get thrown into the layout in InDesign since for example we have

p: 02 9805 0633 f: m: 0405 195 022

or worse the number sets have been mangled and we get:

p: 2 9805 633 f: m: 405 195 22

What I have had to do is enter the numbers in the spreadsheet pre formatted:

column for phone therefore is text:
"p: 02 9805 0633"
fax is completely blank and mobile is
"m: 0405 195 022"

InDesign handles this ok if I run a grep/replace for two m_spaces into one.

But what I want to do is run a function or script of some kind from the raw spreadsheet data (because I will ultimately get this from a php engine)

and from

2, 9805, 633

create

p: 02 9805 0633

ie test several things and ensure that the phone is correctly formed:

start with "p:" check that the area code is "02" and of only "2" make "02", add a space, ensure that the second set of numbers is 4 digits, and the second is 4 to test if it has a leading 0 dropped and so on...

Gee, guys this sounds harder to explain than it would be for someone experienced with this stuff and once I know how to do it the logic will fall into place but so far my Excel 2007 skills have left me clueless..

Any assistance, pointers and mentoring would be most appreciated!

Tia

Will


I have data that is in one row and needs to be seperated into several rows. see example. In this example I would need 4 separate rows for the 1st line and 5 seperate rows for the 2nd line . Each row would be identical to the root except for column C. Column C is the factor that determins how many rows needed. I would need a row for each number that is seperated by a semi-colon.

Iraq Mobile 73-75 96473;964730;96475;964750
Iraq Mobile Asia Cell 96477;9647701;9647702;9647703;9647704

The final out put should look like the following.

Iraq Mobile 73-75 96473 Iraq Mobile 73-75 964730 Iraq Mobile 73-75 96475 Iraq Mobile 73-75 964750
Iraq Mobile Asia Cell 96477 Iraq Mobile Asia Cell 9647701 Iraq Mobile Asia Cell 9647702 Iraq Mobile Asia Cell 9647703 Iraq Mobile Asia Cell 9647704
The file could be thousands of rows of data

Any help would be appreciated. Thanks David