Importing Phone Numbers And Contacts
How do i go about importing contacts - names, numbers, addresses that are in excel into Microsoft Outlook?
View Complete Thread with Replies
Related Forum Messages:
Cleaning Up Phone Numbers With VBA
I 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:
276 623 4254
410 612 1100 Rob
(413) 786-1636 Cindy
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.
Find And Replace Phone Numbers
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.
Columns To Phone Numbers Format
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.
Using Vlookup To Find Phone Numbers Online
How 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.
Finding Country Based On Phone Numbers
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.
Formula Or Macro To Fix Phone Numbers
I came up with this formula trying to fix phone number but it’s not working properly
=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
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.
Taking Area Codes Off Phone Numbers
I have a column full of 10 digit pone numbers. All different numbers and many different area codes (No dashes):
Is there a way to remove the three leading numbers from all of the numbers at once for the whole column?
Count Valid Phone Numbers And Email Addresses
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.
Extract Fax & Phone Numbers From Cells
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 ....
Split Phone Numbers Into Single Column & Remove Duplicates
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:
The results on the next spreadsheet need to be:
I am looking for a way to automate this process.
Importing Measurement Fractions As Numbers Not Dates
I am creating a spreadsheet for use in a manufacturing environment to create a costing and cut list spreadsheet for production set up. The intent is that anyone can use this by copying and pasting an Excel Bill Of Material (BOM) that is generated out of a CAD program onto sheet 1. On sheet 2 (which they don’t touch) is the costing and cut list spreadsheet that references sheet 1 and automatically generated the numbers to create cut sizes and costs.
The first challenge I had was that the measurements include the inch symbol. For example if the imported text 15 5/8" is in cell A1 I am able to remove the inch symbol by using the following formula on sheet 2: =--LEFT(Sheet1!A1,LEN(Sheet1!A1)-1). The result is: 15.6250 which is exactly what I want.
Here is the problem I am having. When the listed dimension is less than 1” and is 5/8” for example I get ###### because I have the cell formatted as a 4 decimal place number. If I change the formatting to number with zero decimal places it becomes 39941 which is how many days it has been from January 1, 1900 to May 8, 2009.
The question is what formula should I use to change the 5/8” imported text so it says 0.6250 and can be used in math formulas? Please keep in mind that the imported text could be a measurement with a small number like 1/16” to a large one such as 284 3/4" and that the imported text “is what it is” and cannot be manually changed or adjusted (because that presents the risk of people creating errors and costing the company money by ordering the wrong material sizes).
Number Of Unique Contacts
Client Id Contact No. Unique Contacts 111123 12 3 111123 12 3 221123 2 2 111123 34 3 111123 5 3 221123 1 2 821123 4 1 221123 2 2
I am trying to calculate the number of unique contacts that each client has made. So in the example above Client No. 111123 has made 3 unique contacts - numbers 12,34 and 5.
However I'm not having any luck - as I suspect it will be a fiendish array formulae. Alternatively I'd settle for some VBA.
Contacts From Worksheet To Outlook
I have been trying to use VB to send contacts from contacts.xls to outlook. I have some code from MSDN that shows me how to put in ONE contact (which is already declared in the code). Well I was thinking of writing something to take a worksheet that has x number of contacts and automating x number of contacts. The problem is, I am familiar with using SQL to pull records, but I was thinking there was something easier like importing the excel namespace. So I decided to try it out this way and found this little tidbit of code which is:
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = New Excel.Application
xlApp.Visible = False
xlBook = xlApp.Workbooks.Open("contacts.xls")
xlSheet = xlBook.Worksheets(1)
'READ FILE HERE
Dim pXLRange As Object
pXLRange = xlSheet.UsedRange
Dim i = 0
Dim j = 0
For i = 1 To UBound(pXLRange, 1)
For j = 1 To UBound(pXLRange, 2)
It gives me this error for the FOR i=1 to UBound line:
Unable to cast COM object of type 'System.__ComObject' to class type 'System. Array'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
here's the namespaces i have imported:
Imports Outlook = Microsoft.Office.Interop.Outlook
Imports Excel = Microsoft.Office.Interop.Excel
Tranpose Mulltiple Contacts From One Row
I would like to ask about How to Tranpose mulltiple contacts from one row.
Company1Firstname Lastname1 Firstname Lastname2 Firstname Lastname3Title1 Title2 Title3Company2Firstname Lastname1 Firstname Lastname2 Firstname Lastname3Title1 Title2 Title3
result need to be.
Merging Workbooks: So All The Information I Have About These Contacts Is In The Same Book
I have 2 books right now. Each book has 2 colums of data (See attached jpg.):
Book 1 has a column for "phone number" and another for "street"
Book 2 has a column for "phone number" and another for "house number"
I would like to merge these books together so that all the information I have about these contacts is in the same book. Column A will be the phone numbers, column B will be the street name, and column C will be the house number. As you can see by looking at book 2, I only have house numbers for some of the phone numbers in book 1. My goal here is to have book 2 "look at" book 1, and add any information book 1 does not have (such as house number) to the relevant row (the row where the phone numbers match). This new book will by my book 3. I've attached a jpg. with how book 1 looks, book 2 looks, and how I want book 3 to look.
Extracting Phone # And Name
I have this data in cells A1-A7 (each line in the example below is a cell with the cells name to the left of the: and the value to the right of the
The colors are simply to show the matching data----there are no colors in the excel sheet
A2:8888 bla oook nikk Rd $15,950,000
A4:5+3. 13+ acre horse ranch in
A5:private canyon. Pool, barn/ more.
A7:Cell7 is blank
I wish to extract the Phone#'s and Names to columns B&C.
The data for this example would be broken down as follows:
Phone Toll Formulas
I had two formulas for working with phone tolls. I moved offices a while back and can not find the disk I burned them to, and I cant remember them.
One of the formulas was like this: If the cell contains and or equals target number than put “incoming” (and/or if not than “outgoing”) in this (a different) cell.
I also had a formula that if the phone numbers came in 3 columns i.e. area code in one column, prefix in a second column and the last four digits in a third column, would combine all the numbers in one cell.
Phone Number Formatting
I have a cell that NEEDS to be of the following format:
I have set this up under formatting, custom... and when I type a number like this ...
01304999999 I get the result 01304 999999 PERFECT!
Unfortunately if I type this....
01304 999 999 I get this result 01304 999 999 which ain't good as it mucks up my Macro, whih incidentally is this...
appWD.ActiveDocument.Bookmarks("PMTelephone").Range = Format(strPMTelephone, "0#### ######")
4 Column Phone List
I have just created a phone list of about 70 lines on a single A4 sheet
A B C D
Name No Name No
One of the PA's now wants to know if she deletes a Name and No from say column A & B
can I make it all reconfigure to eliminate the blank spce.
So basically names fill column A & B first then overflow onto Columns C & D.
I think a macro is the only way to go here but the PA isn't keen.
Autofilter Phone Number
I am building a contact database in excel. I am using a userform to search, by autofilter, through several thousand contacts by any one of 7 criteria(account name, postal code,, account number, etc.). The only criteria that I cannot get to work is the phone number.
The numbers are entered in a ten digit string and the cells are formatted as phone numbers. i.e: 8005551212 = (800) 555-1212. When I run the search by the 10 digit number, the autofilter hides all results.
Phone Number Macro
I often have lists of badly formated phone numbers. I have created a number of macros that are quite good, but there is room for improvement.
Delete non numeric characters. This is a weak point - at the moment it is not working well (it removes characters from the entire sheet, rather than selected cells)
Dial Phone Number
I'd like to dial phone numbers directly from a specific column on a worksheet just by doubleclicking on them.
I found this code which I am trying to adapt to my use.
The hardware part is setup already (modem, phone line, etc.)
Private Sub Dial(Number)
Dim DialString As String
Dim FromModem As String
Dim dummy As Integer
' AT is the Hayes compatible ATTENTION command and is required to send commands to the modem.
' DT means "Dial Tone." The Dial command uses touch tones, as opposed to pulse (DP = Dial Pulse).
' Numbers is the phone number being dialed.
' A semicolon tells the modem to return to command mode after dialing (important).
' A carriage return, vbCr, is required when sending commands to the modem.
DialString = "ATDT" + Number + ";" + vbCr
I am getting an error message when I doubleclick that says :Runtime Error 424 , Object
Required and then I'm taken to the code line that is in Bold Yellow. Dealing with Comm
Ports is too much for me. I only know that my modem is in Com Port 4. If I'm told what to
Macro That Will Convert Phone #s
I'm needing a macro that will convert a column of 10 digit phone numbers and break them out or convert them from a XXXXXXXXXX format to a XXX XXX XXXX format.
I want to be able to copy and paste a long column of 10 digit numbers then have the macro run down the column and cut or copy the area code (first 3 digits of the 10 digit string) - move it two columns to the left - paste it into that cell - return then cut the next 3 digits - move it one column to the left, and paste it.
Phone Number Formatting ..
I have a list of telephone numbers... in every format you can imagine
(123) 456 - 7892
is there any way to extract JUST the numbers...
so the format all phone numbers would take would be:
Removing 1- Prefix In Phone #'s
I read the thread below on how to utilize the Subsitute function to remove periods and thought about being able to use it for this. However, I have some phone #'s in my list that contain multiple 1- scenarios in them because the area code or 3-digit prefix sometimes include a 1- also. How do I make the formula only look at the 1- for long distance and not any other 1- found in the phone #? I want to remove all of the 1- for long distance because we are trying to use a new autodialer that is pre-programmed with the 1-.
Send SMS Message By Phone
I would like excel to send an SMS text message (via Skype maybe?)when a particular set of conditions arise in a spreadsheet. I am not sure if this would be best carried out with spreadsheet functions or vba, or if it is possible at all.
Seperate Email From Phone Number
I have a list that i copies from the web which was pasted in this format in 1 cell
i wanted to seperate the email into its own cell, how can i write a macro that will take anything from the right of the last number and move it into its own cell?
Adding A Zero At The Beginning Of An INTL Phone Number
I have a very long list of international phone numbers in a .csv format that I need to put a zero in front of each one. We are doing an international autodialer campaign for an upcoming event and need the zeros at the beginning to pay for the call. How do you get around Excel not allowing a zero at the front of a number and the .csv format not allowing you to save certain formats? I've been searching the archived posts for a hour now and can't find anything on this!
Here is an example
Comparing A Phone Number To An Allowed List
I have our company telephone accounts and im trying to highlight any numbers that are not on our approved dialling list. What I would like to happen (ideally) would be for a formula to look at the cell next to it and compare it with the approved numbers list, if it doesn’t appear on the list then it would do something to let me know, like put a big red X in the cell (if it could make me a cup of Tea instead that would be better).
Reference Function: Pickup Groups And Members Of Each Group From A Phone System
I have the following worksheet, which lists the pickup groups and members of each group from a phone system. Another worksheet lists all the exts and I want to have some sort of VLOOKUP, MATCH function that will look at the ext number and come back with the pickup group number.
I've attached a copy of the spreadsheet to show you what I'm talking about. So looking at the spreadsheet if I have an ext number of 8001 I want to return the value of 2 as ext 8001 is a member of pickup group 2. Also of an ext is not a member of any group, then I want to return nothing.
Conditional Copying - Variable Ranges - Sprint Cell Phone Usage Import
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
City Called from
State Called From
Calculate Work Hour Phone Time By Day, Week & Month
I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
Importing Value From One Workbook To Another
I have a Excel workbook (wb1) where the columns are showing the months and the rows are showing different actions/activities. If an action takes place in a specified month the cell corresponding to both the action and the month is marked yellow.
In another workbook (wb2) I want to be able to retrive data from wb1. What I want is that when wb2 is opened it should do a search in wb1 to find the first action/activity that takes place in the current month. The activity/action text should be imported to a textbox or cell in wb2.
I am trying to import from Excel Worksheet 1 into Excel Worksheet 2 in a user-friendly manner. I've created a form in Excel Worksheet 2 that allows the user to browse for a file and select it.
All I want to do is copy columns A through I from Worksheet 1 into Worksheet 2. How can I do this very smoothly without needing to open up Worksheet 1?
Importing A File
How can I limit the number of lines when importing a file? Limit the import
to the first 53 lines of a file that has 50,000+ lines.
Want to import the first "XX" set of lines, then I want to skip a set of
lines, then continue to import the rest of the lines. The second import needs
to be inserted 2 or 3 lines down from the first import.
There is the function:
But it only has a StartRow and can not specify the "EndRow". Also can not
specify where to start in the worksheet, i.e. Cell A5
Importing From Some Other File
I have made a macro to import the a excel file into a new file after truncating few initial rows. Here is the code for it:
Dim Prompt1, Prompt2, Prompt3, myDir, myFile, mySheet As String
Dim ClCnt As Long 'column counter
Prompt1 = "Enter the location of input file:"
myDir = InputBox(Prompt1)
Prompt2 = "Enter filename:"
myFile = InputBox(Prompt2)
Prompt3 = "Enter sheetname:"
mySheet = InputBox(Prompt3)
With Workbooks.Open(myDir & myFile).Sheets(mySheet)
ClCnt = .Range("A33:CB33").SpecialCells(xlConstants).Count ' Count the number of columns present..................
I am having a little trouble with this piece of simple code that just seems to be not working the way i want to. I can't figure why it won't work.
Private Sub CommandButton1_Click()
for some reason if i try to do Range("B5:F19").Select and then do Selection.Copy, I get an application error message. This has never happened before and for some reason it just started to act up. If you want to know what it does when I run this macro.. it actually doesn't go to Sheet "Global" it stays in the same sheet and does all the copying/pasting/clearing in the same sheet as the button is. I made sure that the tab is "Global" and i have confirmed caps. spaces everything.