removing any letter from string
 

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

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

removing any letter from string
 

Hi,

Try this function

call with
=RemoveCharacters(a1)

Function RemoveCharacters(rng As Range)
FinalString = ""
For x = 1 To Len(rng.Value)
If Mid(rng.Value, x, 1) Like "[0-9]" Then
Character = Mid(rng.Value, x, 1)
Else: Character = ""
End If
FinalString = FinalString & Character
Next x
RemoveCharacters = FinalString
End Function

Mike


"God Itself" wrote:

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

removing any letter from string
 

On Sat, 6 Dec 2008 22:51:11 +0100, "God Itself" wrote:

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

========================
Option Explicit
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^\d;]+"
RemAlpha = re.Replace(str, "")
End Function
=====================
--ron

removing any letter from string
 

Are the numbers you want **always** enclosed in parentheses and, if so, will
those parentheses be the only ones ever in the text? If yes, try this...

Function GetNumbers(S As String) As String
Dim X As Long
Dim Nums() As String
Nums = Split(S, "(")
If UBound(Nums) 0 Then
For X = 1 To UBound(Nums)
GetNumbers = GetNumbers & Val(Nums(X))
If X < UBound(Nums) Then GetNumbers = GetNumbers & ";"
Next
End If
End Function

--
Rick (MVP - Excel)


"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