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 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:
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'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
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)
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.
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 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
I have a list that i copies from the web which was pasted in this format in 1 cell
(214) 880-4000lee_ainslie@maverickcap.com
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?
I have the following text, in which I need separated into three columns.
Column A) Company Name Column B) Address Column C) Phone Number
I was thinking perhaps I could replace "-------" to just one "-" and use that delimiter to separate the phone number.
Then Use *** Company Name Here *** --> *Number Variable* and filter the rest..
But I don't see it being able to work.Here is the text I'm looking to format (Mind you there is about 8,000 Records. And, it's all formatted like below. (Company Name, phone number being on different lines and phone number being separated by dash's
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).
Is there a way to extract a phone number out of a string of text. The phone number will always be expressed the same way (eight digits seperated by a space half way through) - 9999 9999. But the text before and after the number is random. Is there a macro to do this on a large scale? The method needs to be applied to hundreds of these strings, each on a new line.
An example of the string of text with the phone number underlined:
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!
We have a spreadsheet of our customer's info, to send to our collection agency; when we export it puts parenthesis around the area code and adds a hyphen in the number.
Also it puts commas in the figures. And I need the slashes gone from the date - is there a simple way to do this?
I tried formatting but it doesn't take out the () or ,
I am trying to format a cell (in Excel 2010) as a phone number. The problemis that the data comes over from our download as text. Is there an easier way then using the 'SUBSTITUTE' function to change format to a true phone number (so that we can mail merge into WORD)? Since it is extracted out as a text, using the 'phone number' format (under special) will not work. Using the 'SUBSTITUTE' formula to remove the parenthesis', the dash and the space work but is cumbersome.
Below is an example of the text format that is being extracted: (706) 378-7585
I have a series of dependent drop lists and a cell which asks for a phone number. I would like if the selection in the previous cell is North America, the format of (111) 111-1111 is chosen. if the user enters the improper format I would like them to be prompt with an error "Improper Format - <Proper Format>", clear contents, and re-select the cell otherwise if valid select the next cell. It would need to strip out the leading 1 incase an international code is entered and also special characters such as "(",")","-","+","_" as well as spaces.
The next problem is that it would have to change format to if another selection is chosen. The other two selections are Australia and Europe. Europe is more difficult of a format as the area codes vary in length. I will be dealing predominantly with Sweden whose area codes even vary. For Australia I would like it to be in the format 011 61 1 1111 1111 where 011 61 will be added even if the user does not enter it (or if not possible prompt with proper format).
The cell asking which continent is G35 and options are "","Australia", "Europe", "North America"
I am basically doing some housekeeping and trying to reduce my number of code lines. Can this bit of code be done a bit simpler?
For r = lrow To 15 Step -1 If Range("N" & r).Interior.Color = vbRed Then Range("N" & r).Interior.Color = vbCyan Range("N" & r).Value = "Triangular" End If If Range("V" & r).Interior.Color = vbRed Then Range("V" & r).Interior.Color = vbCyan Range("V" & r).Value = "Triangular" End If Next r
I have a database of sales records containing 6 address columns (C-H). I want to extract the country (which may be entered in any of the 6 columns) and place this in a separate column. I have a definitive list of countries on sheet 2 which I have saved as a named range (Country).
I would like to be able to set something up which searches the six address fields on sheet 1 and if a match is found against the Country range, enter this in a separate column (I). A non-code solution would be ideal as I am nowhere near that level yet and want to be able to understand what I am doing as far as possible!
I frequently use the record option to start off my macros. I realize this usually results in recorded events which are not absolutely necessary (recording a print macro is one that is full of excess line items). I then try and combine what I have recorded with what I have garnered from this wonderful website. If my spreadsheets are small - and I am not writing code to pass any inspections - is there a need to go back and clean up?
I am using a number of lists that act as filters for a range of cells. For the filters, I have a few selections for each and I also would like to have an all filter (no filter) but without the formula being too lengthy (or code). Is there an easy way to be able to not use a specific filter if a certain selection is made ("All" for example)?
However I have survey data results and in one of the cells it has multiple values which are separated between ; and some are not separated at all e.g B&Q; The Range; Wicks The Garden Shop
Also there are spelling mistakes everywhere and variation of the word B&Q e.g b+q, B n Q
I need to add count up all of the B&Q, Wicks etc...
Is there a formula where I can take a zip code, and for any zip codes that have the 4 digit zip code extension, to remove those 4 digits, and to add a zero to the beginning of any zip codes where the zero was cut off. Is there a formula I can run my zips through to do both of those tasks?
I have several time series in excel pulled from a datasource on a daily basis, however some series include weekends, some dont and some others have a few days missing. What I did is I already have a column set up with only weekdays on the other sheet, what I am trying to do is match the data with the dates.
The obvious choice is vlookup, but given that I have over 50 time series and the time span is over 10 yrs, its not the optimal solution...it will crash the sheet i think. I also tried a combo of match and index...but same...too many formulas. I think I need a macro. Does anyone who analyzes time series have a "cleaning" code that he oe she can share with me?
