Excel - Remove text from a number 2

[slickp]

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

 

[SkipVought]

Hi,

How about Edit/Replace?

Skip,
[sub]
[red][/red]
[/sub]

 

[slickp]

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

 

[KenPuls]

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

http://www.excelguru.ca

 

[firefytr]

Here's a UDF for it as well:

http://www.mrexcel.com/td0078.html

which you may use after you run Ken's routine to keep up on your clean up.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

"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]
[red][/red]
[/sub]

 

[firefytr]

Some more good examples here, both code and formula:

http://www.mrexcel.com/board2/viewtopic.php?t=54698

(fast UDFs & formulas)

http://www.mrexcel.com/board2/viewtopic.php?t=72768

(Reg Exp)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[KenWright]

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..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

TRS 80 -- Ha!

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

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

Skip,
[sub]
[red][/red]
[/sub]

 

[KenPuls]

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

http://www.excelguru.ca