Cleanup Telephone Numbers Have Some Text Data?

Aug 16, 2014

I have some cells which contain telephone number and some text data. They look like this.

(713) 555-4600 (Work)
(713) 555-4620 (Home)
(832) 555-4610 (John's Phone)

I need to strip out the text data and just display the phone number as follows

(713) 555-4600
(713) 555-4620
(832) 555-4610

View 4 Replies


Formating Telephone Numbers

Feb 9, 2005

Is it possible to format cells so that inputed tel. numbers are all have the same format and not just appear the same, regardless of how they were typed in?
i need it so the column can be sorded by phone #.

View 9 Replies View Related

Removing Telephone Numbers From A Cell?

Jun 1, 2013

I have a limited amount of VBA knowledge. My limited knowledge is based on solving my problems by looking at the answers already on this site and trying to adapt it to my needs. Here goes:

I import a list of address and telephone numbers (100+) into excel. example - Cell A1 contains "10 downing street, whitehall, london, SW1A 2AA 01234 567890", sometimes there is another space after the number, sometimes there is no telephone number. I want to remove the telephone number at the end if it is there. Have searched the forums have have come up with the follow solution:

1. import list to column A
2. copy and paste Trim to column B
3. copy values in column B and paste to column A
4. delete column B
5. remove the end numbers
6. copy and paste Trim to column B
7. copy values in column B and paste to column A
8. delete column B
9. remove the end numbers

It looks like this in code:

Sub testa()

'select (=TRIM(A1)) formula and paste to sheet
Application.CutCopyMode = False


'copy values of column B to Column A and delete column B

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


'copy values of column B to Column A and delete column B

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

I know this is a long winded way around (still chuffed I worked it out by myself though!). If I merge all the macros into one marco it continually loops and deletes everything in the cells and I have to press escape to stop it.

My questions are:

1. Why, when I merge all the macros doesnt it work the same as individual macros? (I would like to know for future reference).
2. Its there a better way to remove the telephone numbers?

View 3 Replies View Related

Insert Spaces In Telephone Numbers

Aug 24, 2007

I have a column that contains all telephone numbers without any spaces - 01234567890

I need to have at least one space inserted anywhere in the number i.e. 0 1234567890 or 01234 567890.

View 8 Replies View Related

Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

View 9 Replies View Related

Data Cleanup From List With Multiple Entries?

Apr 24, 2014

I have a large database of service invoices. Since our invoice can carry multiple lines, the table in question might have multiple entries for the same invoice number. I'm trying to generate a performance metric dashboard for the service group by employee. Again, the employee can show up multiple times per invoice if he performs different work for each invoice.

I'd also like to condense the list down so there aren't any blank rows between the rows with data.

Ultimately, I need to find each individual invoice that each employee worked on and generate a list from which I can then generate an SPC chart. I'm willing to do this in a couple of stages if necessary. Primarily, I want to avoid using VB script if possible.

So, cherry pick service invoice numbers from a list when matched to an employee's name (VLOOKUP), consolidate multiple entries when that employee's name matches multiple entries of the same service invoice number, and condense the list (preferably as it's built) so there are no blank rows.

View 3 Replies View Related

External Data - Cleanup Existing Connections

Apr 26, 2013

While making my first foray into defining external connections (to an Oracle database) I ended up with an "Existing Connection" that reports "File could not be opened. It is either damaged or the file format is not valid. Try opening the application that created it and saving to a new file." Followed by "Unable to open or read this connection file. Either the file has been damaged or the file format is not valid."

Ok, fine. So how do I delete it? There seems to be no option for that in the "Existing Connections" dialog. A search for a *.odc or *.udcx file doesn't turn up anything with the name of this connection. A search of the registry doesn't turn up anything.


View 1 Replies View Related

Extracting Numbers From Text And Turning The Numbers Into Rows Of Data

Feb 23, 2010

The format of the text in which I need to extract numbers is as follows:

23411268 - 23411270

Need to extract the following:


These numbers have to be listed in three seperate rows.

View 14 Replies View Related

Formulas For Cleanup

Jan 21, 2010

I have macros that format them accordingly, run formulas, cleanup, etc. My problem is that my View Macro screen to run them has gotten very cluttered and confusing. I've tried looking for some sort of add-in that might make for a better way to organize them that's more visually appealing, but can't find anything. I also tried searching this forum as well. Am I just being picky? How do you guys that have 100's of macros organize them all?

View 5 Replies View Related

Telephone Dialing Code

Jan 2, 2008

This has definitely been done before, but I am not sure if it has been done through Excel/VBA.

I have a 9 or 10 digit telephone number, preceded by a zero and what I want to do is get Excel to figure out the combinations of text characters that will allow me to have a text phone number.

For Example:
0-800-43573375 is the same as 0-800-HELPDESK

View 9 Replies View Related

Cleanup Word Document Which Is Created Via Excel?

Feb 20, 2014

I have an Excel spreadsheet that contains information for a Word document. It uses an elaborate macro to "substitute" unique codes in a starter document. At completion of the process, it goes through and delete all unused codes

The problem is that the codes are preceded by a "bullet" symbol.

How can I remove that orphaned bullet symbol. If the code is ". CtlCode10" (where the . is a bullet), then ALL need to be removed

View 2 Replies View Related

Data Validation: Allow Between 2 Numbers & Text

Nov 22, 2006

Is it possible to force a cell entry (using Data Validation) to either a whole number or a value? In other words, I would like the data in a cell to be either a whole number between 10,000 and 20,000 or "N/A". I can do it with VBA, but Data Validation (if it's possible) would be better.

View 2 Replies View Related

Data In Column - Extracting Text And Numbers

Jul 14, 2012

I have the following data in column b and I would like to extract into individual cells.

I would like to extract upto the first number, number text after first number etc

I have several rows and the data lengths are variable

Liverpool FC 2 Sheffield United 1
Manchester United 0 Everton FC 3
Middlesbrough 2 Manchester City 0
Norwich City 2 Chelsea FC 1
Oldham Athletic 1 Crystal Palace 1
Queens Park Rangers 3 Southampton 1

View 6 Replies View Related

Data Validation Allowing Both Text And Numbers

Aug 28, 2007

to validate a cell for the following customer format, but cant get it to work:


Where XX is any combination of letters (upper or lowercase) and
00000 is a combination of any 5 numbers.

It must always be in this format, with this number of digits in total (2 letters followed by 5 numbers)

View 9 Replies View Related

Counting Consecutive Cells With Data (Text Or Numbers)

Feb 7, 2012

Say the Range is (D3:M3) there is data in D3,F3,J3,K3,L3, and M3. I want to count consecutive cells with data from M3 backwards to D3. In this scenario the sum would be 4.

View 7 Replies View Related

Excel 2010 :: Formatting Numbers Written In Text To Numeric And Pulling Only Numbers Out

Feb 20, 2013

I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.

e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..

I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same

How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?

View 3 Replies View Related

Import Large Text File Of Numbers Without Excel Splitting Numbers?

Jul 19, 2014

I have a text file with rows and columns of numbers ranging from 1-4 digits that I'd like to import/copy into Excel with each number being in its own cell. But whenever I copy/try to import, Excel splits all of the 3-4 digit numbers up into single digit numbers. The text file has 10,000+ columns (each number occupies two columns so I have half of that amount in numbers) and 300+ lines.

Is the file simply too large for Excel to handle or is there a way I can do this?

View 14 Replies View Related

Convert Numbers Stored As Text To Numbers (attached File)

Oct 22, 2009

In the ID column of the attached excel file whenever I convert the numbers stored as text to numbers it results in a weird transformation.

e.g. an ID of 480610141001 becomes 4.8061E+11. When I do the same process in the name column, which has similar numbers, it will give the correct result following the same process.

View 2 Replies View Related

Macro To Convert Numbers Stored As Text To Numbers?

May 23, 2014

I have been looking through the forums and found the below code, but I have both text and numbers in the same range. I have attached an example of what I need to have converted.


View 3 Replies View Related

Extract Numbers From Text Para And Sum Extracted Numbers?

Jan 27, 2012

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.

View 9 Replies View Related

VLookup For Numbers Contained Within Cells With Numbers And Text

Sep 26, 2012

23445566894 Brwn pdc aft
45687930596 Gld wdget adi

In the example above, I would like to do a vlookup in column C that matches the digits in column A to those in column F, with the output being the corresponding value in column G. I believe it involves the left function but not sure how to really use it here.

View 9 Replies View Related

Making Sure Numbers In Column Are Formatted As Numbers And Not As Text

Jun 27, 2013

I'm working on a spreadsheet with about 400 rows and 10 columns. All the numbers in the columns are formatted as text, which doesn't allow me to use AutoSum. I have selected the cells and formatted them as number with no decimals, but AutoSum still doesn't work because the formatting hasn't changed and they're still formatted as text. I know if I double click each cell, the cell changes the format from text to number, but that will take a lot of time to change 400 cells in each of the 10 columns. What can I do to quickly reformat the cells from text to number?

View 1 Replies View Related

Sort Mixture Of Numbers And Text Using Numbers And Decimals

Nov 13, 2013

I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)


Col A Col B
1Billing Accuracy
2Billing Time
3Credit Check Accuracy
4Credit Check Time
2.1Bill preparation


This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows ( through are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.

View 9 Replies View Related

Compare 2 Columns For Numbers In Mixed Text & Numbers

May 3, 2006

I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.

View 8 Replies View Related

Converting Numbers Stored As Text To Numbers Via Macro

Feb 9, 2007

I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.

View 7 Replies View Related

Convert Downloaded Web Page Numbers Seen As Text To Numbers

Jan 16, 2008

See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?

View 5 Replies View Related

Copy Only Numbers Within A Cell With Text And Numbers

Mar 25, 2014

I am looking for a formula to grab only the numbers withing a cell that has text in it i.e (John Peters142 lp) or (Sally Jones54) or (Terry Cram310 jkb) sometimes there will be text after the number and sometimes not, the format is as shown with no space between name and number but a space after the number.

I added a sheet for visual example.

View 2 Replies View Related

Converting Decimal Numbers To Text With Dot Numbers

May 27, 2006

we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:

Sub ForceToNumber()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
With wSheet
. Range("IV65536") = vbNullString
.UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End With
Next wSheet
End Sub

Source : The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

View 3 Replies View Related

Convert Numbers, Seen As Text, To True Numbers

Aug 26, 2006

The back story is that from a system we use, when you download data from inside it, Excel treats most of the cells with a "Number Stored As Text" error. I had problems with this and it was just generally annoying so I wrote the below macro that converts each of the cells back to general cells and reinputs the data, essentially reseting the cell and keeping the data. Being that this is a loop, it takes a good while to run if alot of data was downloaded. Any idea's on how to speed it up besides screen updating? Note, other stuff happens in the first macro, but it is just cosmetic non-intensive things. It calls the second macro because I wanted them seperate should I want to just run the second.

Sub CleanData()
Application. ScreenUpdating = False
' Find the Right most cell and the Bottom most cell and
' run the macro from A1 to this cell recursively
Col = Selection.Column
Row = Selection.Row
Range("A1", Cells(Row, Col)).Select
Application.Run ("FixTextAsNumberError")
Application.ScreenUpdating = True
End Sub..............

View 5 Replies View Related

Match (text) Numbers To Numbers From One Cell To Another

Oct 12, 2006

I need the " solutions cells " to match to the " source cells " and return a " yes " or " no ". in cells d4:d10. Ex: #1 cell C4=01234598, it will conduct a search & match of cell A5 which contains the number 123, since cell C4 contains all 3 numbers from cell A5, then the formula would result in a " Yes ". The match has to contain all 3 numbers from the solution cells to the source cells.

Ex: #2 cell C7=67891234, it will conduct a search & match of cell A8 =901. Since cell C7 only contains the 9&1 from cell A8, it would return a " No " because cell C7 did not contain all 3 numbers from cell A8. I'm not sure if the IF, Match, Index or Count formula is the right one to use, they all seem to partially work, but still don't complete this problem.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved