Making worksheet function to strip a cell of non number characters. 5

[mjstout]

Hi,

I need to pull phone numbers from a cell in an Excel worksheet into another worksheet and eliminate non number characters. Copy paste won't work for me.

There isn't a function in Excel that does this so I have to create my own. Substitute is the closest thing in Excel I've found but I have to tell it each specific char to omit.

WI'm trying to do is analyze the number (ex. 916/753-1593) and make any characters that had ascii value other than 48-57 = "". The ending result would be 9167531593 .

Am I on the right track?
Any advice?

Thanks,

Mike

 

[Andrzejek]

This is a VB 6 code, but it should work for you, or at least give you an idea what to do:

Dim strTel As String
Dim strNewTel As String
Dim i As Integer

strTel = "916/753-1593"

For i = 1 To Len(strTel)
  If Chr(47) < Mid(strTel, i, 1) And _
     Mid(strTel, i, 1) < Chr(58) Then
      strNewTel = strNewTel & Mid(strTel, i, 1)
  End If
Next i

MsgBox strNewTel

HTH

---- Andy

 

[AnotherHiggins]

Here's a modified version of Andy's code that will loop through all cells you have selected. So if you have data in A1:A1000, just select those cells and run this code.

Dim strNewTel As String
Dim i As Integer
testvar1 = Chr(47) < Chr(48)

For Each cell In Selection
    For i = 1 To Len(cell)
      If Chr(47) < Mid(cell, i, 1) And _
         Mid(cell, i, 1) < Chr(58) Then
          strNewTel = strNewTel & Mid(cell, i, 1)
      End If
    Next i
    cell.Value = strNewTel
    strNewTel = ""
Next cell

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

I like the Select Case construct. Using John's code...

For Each cell In Selection
    For i = 1 To Len(cell)
      Select Case Mid(cell, i, 1)
        Case "0" to "9"
           strNewTel = strNewTel & Mid(cell, i, 1)
      End Select
    Next i
    cell.Value = "'" & strNewTel
    strNewTel = ""
Next cell

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

 

[Andrzejek]

I just LOVE it - a little piece of code and people improve on it giving somebody a full sollution. That's great!

I just do not know what is this in anotherhiggins' code:
testvar1 = Chr(47) < Chr(48)

---- Andy

 

[DaleWatson123321]

I like it. I like it. ;-)

Thanks Skip and John. Stars to you both.

Regards, Dale Watson

 

[AnotherHiggins]

I just do not know what is this in anotherhiggins' code:
testvar1 = Chr(47) < Chr(48)

Random carp I forgot to delete before copying and pasting.

I like the select method in this case, too, skip. Good call!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mjstout]

Great input from everyone! Thanks a lot!
I'm good now =)

 

[SkipVought]

"Random Carp"

I find those in my fish pond. ;-)

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

 

[PHV]

Why not simply this ?
For Each cell In Selection
For i = 1 To Len(cell)
[!] If IsNumeric(Mid(cell, i, 1)) Then[/!]
strNewTel = strNewTel & Mid(cell, i, 1)
[!] End If[/!]
Next i
cell.Value = "'" & strNewTel
strNewTel = ""
Next cell

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886