String manipulation

[Jack Schitt]

Hi all

Please could someone give me a formula that strips out all non-numeric
characters from a string variable contained in a cell, and returns a
numerical value that contains the remaining characters preserving their
order?
eg:
Cell A1 contains "a1b2c3"
Function(A1) should return 123
Decimal point and sign characters are to be stripped out as if they were
non-numeric, and the returned value will be a positive integer.
Thanks.

 

[Gord Dibben]

Jack

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP

 

[Jack Schitt]

Thanks for that. Any chance of a non-VBA solution? I was thinking along
the lines of an array formula that cycles through
Indirect(Row("1:"&LEN(cell_ref))) coupled with some sort of SUBSTITUTE()
trick, but I keep geting stuck.

--
Return email address is not as DEEP as it appears

Jack

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP

Hi all

Please could someone give me a formula that strips out all non-numeric
characters from a string variable contained in a cell, and returns a
numerical value that contains the remaining characters preserving their
order?
eg:
Cell A1 contains "a1b2c3"
Function(A1) should return 123
Decimal point and sign characters are to be stripped out as if they were
non-numeric, and the returned value will be a positive integer.
Thanks.

 

[Jack Schitt]

PS. would you please convert the Sub to a Function for me, that can be
called from a cell, referring to another cell, that returns the required
value?

--
Return email address is not as DEEP as it appears

Jack

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP

Hi all

Please could someone give me a formula that strips out all non-numeric
characters from a string variable contained in a cell, and returns a
numerical value that contains the remaining characters preserving their
order?
eg:
Cell A1 contains "a1b2c3"
Function(A1) should return 123
Decimal point and sign characters are to be stripped out as if they were
non-numeric, and the returned value will be a positive integer.
Thanks.

 

[Gord Dibben]

Jack

Try this.....from Norman Jones

Function DeleteNonNumerics(ByVal sStr As String) As String
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

Usage is.......=DeleteNonNumerics(cellref)*1

The *1 forces the results to a number(otherwise text)

Gord

 

[Gord Dibben]

Modifications....too early out here on the West Coast.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

=DeleteNonNumerics(cellref)

Forget the *1

Gord

Jack

Try this.....from Norman Jones

Function DeleteNonNumerics(ByVal sStr As String) As String
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

Usage is.......=DeleteNonNumerics(cellref)*1

The *1 forces the results to a number(otherwise text)

Gord

PS. would you please convert the Sub to a Function for me, that can be
called from a cell, referring to another cell, that returns the required
value?

 

[Jack Schitt]

Many thanks for that, Gord

--
Return email address is not as DEEP as it appears

Modifications....too early out here on the West Coast.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

=DeleteNonNumerics(cellref)

Forget the *1

Gord

Jack

Try this.....from Norman Jones

Function DeleteNonNumerics(ByVal sStr As String) As String
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

Usage is.......=DeleteNonNumerics(cellref)*1

The *1 forces the results to a number(otherwise text)

Gord

PS. would you please convert the Sub to a Function for me, that can be
called from a cell, referring to another cell, that returns the required
value?

 

[Jack Schitt]

Out of curiosity, could you please explain to me the difference (if any) in
the effect of the following two lines?:

Function DeleteNonNumerics(ByVal sStr As String) As Long

contrasted with

Function DeleteNonNumerics(sStr As String) As Long

I tried both versions and they both seemed to work (not rigorously tested,
mind).

Thanks

 

[Myrna Larson]

Hi, Gord:

Your code crashes for me, since the function is defined as a long and you are
trying to assign a string to it. So you need to change it to return a string
or variant.

Given that we make that change, I see that if the value contains no digits,
you return the string unchanged. Shouldn't it return an empty string or error
value in that case?

BTW, I wrote a version that converts the string to a byte array so I could
avoid multiple calls to Mid (or Mid$) which create multiple temporary strings,
and to compare numbers rather than using Like with strings. I expected it to
be significantly faster, but believe it or not, it takes ~50% MORE time to
process the string "a1b2c3", so I'm not going to post it. There must be a lot
of overhead associated with conversion to and from byte arrays.

Myrna Larson

Modifications....too early out here on the West Coast.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

=DeleteNonNumerics(cellref)

Forget the *1

Gord

Jack

Try this.....from Norman Jones

Function DeleteNonNumerics(ByVal sStr As String) As String
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

Usage is.......=DeleteNonNumerics(cellref)*1

The *1 forces the results to a number(otherwise text)

Gord

PS. would you please convert the Sub to a Function for me, that can be
called from a cell, referring to another cell, that returns the required
value?

 

[Gord Dibben]

Myrna/Jack

I'm not very good at this, as you can see.

The original code from Norman Jones returned a string which I multiplied by 1
to convert to numeric(see post further down).

I changed that to As Long from As String

Worked for me with the original data which was a1b2c3

I didn't put much thought into it.

Will work on it. Would appreciate any input.

I could de-plonk Harlan and wait for him to roast me<g>

Thanks, Gord

Hi, Gord:

Your code crashes for me, since the function is defined as a long and you are
trying to assign a string to it. So you need to change it to return a string
or variant.

Given that we make that change, I see that if the value contains no digits,
you return the string unchanged. Shouldn't it return an empty string or error
value in that case?

BTW, I wrote a version that converts the string to a byte array so I could
avoid multiple calls to Mid (or Mid$) which create multiple temporary strings,
and to compare numbers rather than using Like with strings. I expected it to
be significantly faster, but believe it or not, it takes ~50% MORE time to
process the string "a1b2c3", so I'm not going to post it. There must be a lot
of overhead associated with conversion to and from byte arrays.

Myrna Larson

Modifications....too early out here on the West Coast.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

=DeleteNonNumerics(cellref)

Forget the *1

Gord

Jack

Try this.....from Norman Jones

Function DeleteNonNumerics(ByVal sStr As String) As String
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

Usage is.......=DeleteNonNumerics(cellref)*1

The *1 forces the results to a number(otherwise text)

Gord





On Tue, 6 Jul 2004 07:09:08 +0100, "Jack Schitt"

PS. would you please convert the Sub to a Function for me, that can be
called from a cell, referring to another cell, that returns the required
value?