Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - Remove text from a number 2

Status
Not open for further replies.

slickp

Technical User
Feb 15, 2006
35
GB
I have a list of telephone numbers imported from a database. However the field in the database it has come from was a text box so there is text in some of the numbers i.e 01234-523532 or (01234) 254525.

How can I in Excel convert these to numbers only i.e. with the examples above convert them to:

1234523532
1234254525

Cheers
 
There are 17000 records, wouldnt this take a long time?
 
Personally, I would use a little macro to do this. Let me know if you need to know where to put this:

[code}Sub Distill()
Dim cl As Range
Dim lChar As Long
Dim lDistill As Long

Application.ScreenUpdating = False
For Each cl In Selection
For lChar = 1 To Len(cl)
If IsNumeric(Mid(cl, lChar, 1)) Then
If Not Mid(cl, lChar, 1) = " " Then _
lDistill = lDistill & Mid(cl, lChar, 1)
End If
Next lChar
cl.Value = CLng(lDistill)
Next cl
Application.ScreenUpdating = True
End Sub[/code]

Highlight your text (only the ones that need doing, do not highlight the entire column), and run this.

If you have thousands, it may be a little slow, but it should get you started.

Ken Puls, CMA
 

"There are 17000 records, wouldnt this take a long time?"

How long did it take you to enter the above response?

Did you try? Probably would take all of 37 5/8 seconds.

Not everything needs a VBA program, but it it is a recurring issue, then turn on your macro recorder and record the Edit/Replace process.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Pretty sure I can get that down to about 26.26343 seconds using Edit / Replace, but that's probably because Skip is still working on a Commodore 64 :)

Regards
Ken..............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 



...which is a [sub]little[/sub] faster than my Sinclair ZX81.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
slickp,

Thanks for the star. Just for the record, though the Find/Replace would be faster... if you know all the characters that you'd need to replace.

The reason I went with the loop, despite the speed issue on 17,000 records, is because I wasn't sure if you'd end up with other alpha charactes in there. If it's just the following: "(", ")", "-", " ", then I agree with Skip and Ken Wright... a find and replace (in a macro) would knock the socks off what I provided you.

The only adavantage to what I gave you is that it would strip (123) 456-7890 of non numbers as easily as it would strip "asd564asd534asd5" or "123.456.7890 x225", etc...

Cheers,

Ken Puls, CMA
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top