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.
I work for a charity and we have a new call list, about 1.2 million phone numbers. We have a list of about 1,000 numbers that we are not supposed to call again. How can I scrub the do not call numbers against the master list to remove both numbers? The master list has names in a column, city in another column, numbers in a column and city in a column. The do not call numbers I have are just listed in 1 column.
View 7 Replies View RelatedI 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).
View 9 Replies View RelatedI have a phone list and I am trying to add a search box to it that when you type in the Employee name, it will return the value of the cell adjacent to the search result so the extension can show up.
I am a VBA Novice, but I have found plenty of code for the searching aspect of it. I am having trouble figuring out how to have the search occur and then return a different value then what was originally searched for.
Ideally I would like the msg box to show both the employee's full name and extension.
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 long list of names, address, city state zip, and phone number, followed by the next name, address, etc. I need to now be able to bring each one into it's own column.
Example:
David Smith
123 Main St.
Denver, CO 12345
123.456.7890
Joe Blow
345 Happy Ave.
Oakland, CA 34567
567.890.1234
I need to convert it so that I would have
David Smith 123 Main St. Denver, CO 12345 123.456.7890
Joe Blow 345 Happy Ave. Oakland CA 34567 567.890.1234
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 am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View RelatedI'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies View Related1. I have a list of several items in sheet 1. Lets say 30-40 rows.
2. I do a frequently update of this list.
3. In sheet 2 I want to have a copy of this list in row 1 from column A to Column x (depends on how long the list in sheet 1 is.)
4. When I change the list in sheet 1, could be in first, middle or last row- mayby several rows. The list in sheet 2 should be updated
5. The update of sheet 2 should insert a new column on the right place, according to the list in sheet 1.
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.
Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)
PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.
2- I have another list with 2 columns which arranged in order. I mean:
A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.
My request is I want to make this list 1 column as arranged.
A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.
3- Macro will add a new line to the end of the above list with this format:
Z00001xxxx
Z00001 is static
xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits.
Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)
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
A1:Nordica Valley
A2:8888 bla oook nikk Rd $15,950,000
A3:Sumik/Arnov 120.443.8976/555.443.8976
A4:5+3. 13+ acre horse ranch in
A5:private canyon. Pool, barn/ more.
A6:Robinson 999.443.8976
A7:Cell7 is blank
A8:Jit/Song 587.678.6788
I wish to extract the Phone#'s and Names to columns B&C.
The data for this example would be broken down as follows:
B1:Sumik
C1:120.443.8976
B2:Arnov
C2:555.443.8976
D1:.5
D2:.5.........................................
I'm looking to see is if there is a way to modify this code to format a phone number as (999) 999-9999. Right now it formats phone numbers as 999-999-9999.
VB:
Sub FormatPhoneNumber()
'Purpose: Formats a telephone number as 999-999-9999.
'In cases where more than one phone number is enter in
[Code].....
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.
Step 1:
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)
PHONE formatting. Is there a way to create a macro to format the VALUE of the phone to be either (XXX) XXX-XXX or XXX-XXX-XXXX ? I obviously can get it to look like this, but the value is just XXXXXXXXXX.
View 13 Replies View RelatedI have been able to successfully code a new email message one little thing I can't figure out is adding 77 plus their extension txtphone.
View 3 Replies View RelatedI'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.
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.
View 6 Replies View RelatedI 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#### ######")
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.
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-.
View 8 Replies View RelatedI 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.
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:
(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 list of telephone numbers... in every format you can imagine
123.456.7892
(123) 456 - 7892
1234567892
123-456-7892
etc...
is there any way to extract JUST the numbers...
so the format all phone numbers would take would be:
1234567892
?
How can I send a SMS message to my cell phone if certain ocurrence happens in a cell. For example, if cell A1 = 1, then send a message to my cell phone.
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
I have an old customer list maintained by my company's previous owner in excel format that mostly stored numbers ###-#### format, but also some in ###-###-#### format.
My new list has always been maintained with numbers in (###) ###-#### format.
I have merged these two lists in a new excel file have begun the tedious process of cleaning up duplicates and bad data.
I have used conditional formatting to highlight duplicate numbers in the appropriate columns (L, M, N, O) in this case. However, it does't find matches in the other formats or perhaps the number with the old area code. I know the area code change could lead to false positives, but they should be minimal as this is a small business with mostly local customers. So what I'd like to find out is a formula for conditional formatting that will compare the last 8 characters in each cell (###-####) so as to eliminate issues of missing or different area codes. I would like it to check all of the columns for duplicate numbers not just within each column or within each row individually.
Also separate but related if possible to highlight a different color any cells with data not in any of these formats or if not in (###) ###-#### format if it's too much for all the formats.
I need to reformat phone numbers in Excel, and I am having trouble doing so. I do not have VBA experience, but I am somewhat familiar with functions in Excel. I need to go from 555-555-5555 to (555) 555-5555
View 4 Replies View RelatedIs there anyway to make phone calls automaticly from a spread sheet
I'm calling old customers and it would be easy to just press a button and it will dial for me