View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Gary Keramidas Gary Keramidas is offline
external usenet poster
 
Posts: 2,494
Default removing any letter from string

these seems to do what you want:

Function RemoveLetters(Rng As String) As String
Dim Tmp As String
Dim i As Long, j As Long
Dim n As Long, k As String
Dim tmp2 As String
Tmp = Rng
n = Len(Tmp)
For i = 1 To n
k = Mid(Tmp, i, 1)
If k = ";" Then tmp2 = tmp2 & ";"
If k Like "*[0-9]" Then
tmp2 = tmp2 & k
End If
Next
RemoveLetters = tmp2
End Function

--


Gary

"God Itself" wrote in message
...
Hi

i wrote such a function

Function RemoveLetters(Rng As String) As String
Dim Tmp As String
Dim i As Integer, j As Integer

Tmp = Rng
n = Len(Tmp)

For i = 1 To n - 1

k = Mid(Tmp, i, 1)

For j = 0 To 9
If k < j Or k = ";" Then Tmp = Application.Substitute(Tmp, k, "")
Next j

Next i

RemoveLetters = Tmp
End Function


and i try to remove any letter from string and leave only numbers

but my function does not work correctly

i.e

string in A1: Hardware, and Plumbing and Heating Equipment and Supplies
Wholesalers (4217); Refrigeration Equipment and Supplies Wholesalers (42174);
Warm Air Heating and Air-Conditioning Equipment and Supplies Wholesalers
(42173)

and i receive ,PEqW(2REqW(2WAA-CEqW(23 instead 4217;42174;42173