I have an excel spreadsheet listing some company contacts i need to improve. At the moment the companies address and telephone number are in the same field c2 all the way down to c2120. I need to take the telephone and fax data out of the field and into column d for all the entries. The phone and fax details are in the cells as follows ....
View 9 RepliesI am using VBA to enter customer information into a Microsoft SQL database. I have an excel sheet with thousands of customers listed. I will be using a While loop to process each line of the sheet. Part of the While loop needs to make any format changes necessary before the data moves over to the DB. So here is the issue:
How do I turn the following phone numbers:
(410) 273-9200
910-944-1232
276 623 4254
410 612 1100 Rob
(413) 786-1636 Cindy
1-800-783-4661
304) 842-5491 Sherry
Into a uniform layout? I would prefer ###-###-####
Each of the numbers above is an example from the list I have. There may be more variations (I have over 20 seperate lists to process, with over 10000 customers per list)
I imagine I need some process that will remove all character except numbers and then break the numbers up to add the hyphens back in at the appropriate places.
I have a bunch of phone numbers that I'm trying to reformat.
Say I wanted to replace any phone number like this: 1##########
With this: ##########
Keeping the numbers intact, just getting rid of the 1 in front of them. Can't figure it out for the life of me.
Just got an answer to my phone number problem of converting two columns of numbers
to a single columns with the form XXX-XXXXXXX. Used =A1 & "-" & B1.
Works perfectly one one spread sheet and not at all in another.
When I enter the formula and hit return, instead of the correct result, all I get is the formula.
The format of the columns is the same on both spread sheets.
Formatting existing phone number. I want to remove first 2 digit from the column with 11 digit phone number ?
View 1 Replies View RelatedI came up with this formula trying to fix phone number but it’s not working properly
FORMULA:
=TEXT(LEFT(SUBSTITUTE(a1, "-", "") & "00##", 10), "###-###-00##")
So basically I need a formula or macro that will fix the phone number for me.
Here is an example
78512312 it should be like this 785-123-0012
Or
785123123 it should be like this 785-123-0123. So if Len is 8 numbers long then insert two zeros counting from right to left. So if Len is 9 numbers long then insert one zeros counting from right to left.
I have thousands of phone numbers that hundreds of them are look like this:
123456-8 that means : 123456,123457,123458 or this
123456-57 that means : 123456,123457
There are not any standard of numbers after dash (-) in my data.
how can I expand them to new "rows"?
bob 123456-8 ->
bob 123456
bob 123457
bob 123458
I'm trying to simplified these numbers so each one only consists 8 numbers from the right w/out the blank space or +,-.
Tracking Number
Recip Phone
495905380312
(65)96502898
304612528629
6598941713
495905380746
+6596813956
486044711528
+65 9459 5540
409709299705
6750 2518
409720592221
65-6381-3351
427249160171
6581233832
How do i go about importing contacts - names, numbers, addresses that are in excel into Microsoft Outlook?
View 2 Replies View RelatedI'm trying to format some cells, its some phone numbers but not in the traditional format (###) ###-####.
I need to get them to appear like this ###.###.####,
I have a column full of 10 digit pone numbers. All different numbers and many different area codes (No dashes):
Example:
3305555555
4583651586
4128563254
Is there a way to remove the three leading numbers from all of the numbers at once for the whole column?
I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.
I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.
How can I use the RAND function to generate a list of simulated phone numbers.
View 9 Replies View RelatedI'm trying to change a
418,3315555
format cell to a regular phone number cell
418-331-5555
I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.
I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.
I have a large list of phone numbers & some of the numbers do not have area codes. The area code is the same for all numbers. Is there a way to add the area codes to all of these numbers without keying all in manually
View 9 Replies View RelatedHow can I create a vlookup function to automatically retrieve phone numbers off switchboard.com using my current mailing list?
I have a list of more than 4,000 of my customers. I want to ad in phone numbers automatically. I currently have the first and last name in one column. The address, city, state and zip are all each in their own column.
I have never use vlookup before, nor have I ever used excel other than for data entry. What is the easiest and most efficient way to do this?
I searched this site and seen vlookup mentioned for this type of automatic data entry. There may be another way or method I am not aware of.
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.
I have several text boxes on a 'picture' which is the format for a business review. The text boxes are linked to cells behind the picture which picks up company names, cities etc...
Two of the boxes have a phone# and date
The linked cells are formatted correctly but obviously the text boxes, pick up the 'values' not the formats.
is there any way for the textboxes to show the values with the proper format i.e.,
xxx-xxx-xxxx
mmm yyyy
i have data stored like:
col1
XXXX1244 50
XXXX1519 60
XXXX1244 50
xxxxx1111 10
xxxxx1519 65
the last 4 caracters are numbers. I need to test these numbers and sum the corresponding values them in a single cell without adding new column(SUMIF like).
so in the above example I need to sum all ending at 1244 or 1519, therefore the sum showed in the single cell equals 225
to extract from a single cell: =VALUE(RIGHT(D8;4))
I tried to use an array formula but it seems to crash if a blank cell is in the array
I have to take a range of phone numbers that I have to port from another carrier and manually type them out or copy into another speadsheet, removing duplicate numbers. For example, I have the following numbers:
55532821055553282108
78132821235553282123
55532821625553282163
The results on the next spreadsheet need to be:
5553282105
5553282106
5553282107
5553282108
7813282123
5553282162
5553282163
I am looking for a way to automate this process.
I have a spreadsheet with Mobile phone numbers in the following format:
+44(0)77 7296 5210
The spreadsheet has 2500 of these phone numbers.
Is there a way to remove the brackets and the +44 to leave:
07772965210
I am using Microsoft Office for Mac 2011
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
I have a text string in cell A1 as below:
repairs booked in Dec,11 (INR 37k)
training fees Dec,11 (INR 42k)test Fee Reimb for 03 emp Dec,11 (INR 56k)
skill fees booked in Jan,12 INR 52k
Reimb for 01 employee in Jan,12 INR 8k fee accounted- xyz INR 250k, Quick solutions INR 52K, ABCD India INR 272K, Layer Technologies INR30K and complex mgt INR 21K
Note: (INR 37K) means negative number i.e. -37
Now my task is to manually total the above amounts in calculator, i.e.
-37-42-56+52+8+250+52+272+30+21 = =550
like this there are 100s of cells, could derive a formula for this task.
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. We have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?
View 8 Replies View Relatedjust got a problem with this guys, sample :
in A1 i copy and paste -> "previous / 12.25 " or " "previous/12.25"
and i want in b1 to only have "12.25".
CAn a formula/macro be provided to extract the numbers (including Decimal) from a cell value containing alphanumbers?
For eg.Down 3,492.00 INR should be extracted as -3492.00
Up3,492.00 INR as 3492.00
Please note that the numbers may be of any digit. If it contains down, then the number should be negative and if UP then positive.
I'm trying to obtain a daily/monthly sales total. As you can see from the sample I've left, I have a number of different sales dep't and have to tally each one, but I have a situation where 1 of the dep't I need to keep a tally (including text) of what the amount refers to (but all in the same column, can't seperate them into different columns.......just in case of an doubts). What I need to accomplish is a formula for the following:
1- that it can recognize AND sum across the values. (TOTALS column)
2- that it can recognize AND sum down the SALES D column.
Sheet1 *ABCDEFG1DATE SALES A SALES BSALES CSALES DSALES ETOTALS22/26/2009$458.00 $23.00 $- * $20.00 Late fee + $30.00 purchase$9.00 = $540.0032/27/2009$875.00 $- * $56.00 $12.00 late fee $100.00 delinquency$43.00 = $1,086.0042/28/2009$1,235.00 $12.00 $42.00 $7.00 vis $16.00 mcd $23.00 amx$13.00 = $1,348.005SUBTOTALS$2,568.00 $35.00 $98.00 $208.00 $65.00 $2,974.00 Excel tables to the web >> Excel Jeanie HTML 4
I want to EXTRACT LAST 4 numbers from a sentence
EX:
A1:what is your name 1234
To be
B1:1234
I have a data set that I imported from Access. One of the columns contains the code for specific work activities, for example 13Z or 9A. I need to extract the numbers only from the cells in that column so that they are in separate cells in a separate column. I've been trying to use left, right, or mid functions, as well as text to columns with varying degrees of success.
View 2 Replies View Related