String manipulation

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.

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

On Mon, 5 Jul 2004 20:24:32 +0100, "Jack Schitt"
wrote:

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.

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
"Gord Dibben" gorddibbATshawDOTca wrote in message
...
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

On Mon, 5 Jul 2004 20:24:32 +0100, "Jack Schitt"
wrote:

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.

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
"Gord Dibben" gorddibbATshawDOTca wrote in message
...
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

On Mon, 5 Jul 2004 20:24:32 +0100, "Jack Schitt"
wrote:

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

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"
wrote:

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?

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


On Tue, 06 Jul 2004 08:38:50 -0700, Gord Dibben gorddibbATshawDOTca wrote:

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"
wrote:

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?

Many thanks for that, Gord

--
Return email address is not as DEEP as it appears
"Gord Dibben" gorddibbATshawDOTca wrote in message
...
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


On Tue, 06 Jul 2004 08:38:50 -0700, Gord Dibben gorddibbATshawDOTca
wrote:

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"
wrote:

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?

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

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

On Tue, 06 Jul 2004 08:42:40 -0700, Gord Dibben gorddibbATshawDOTca wrote:

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


On Tue, 06 Jul 2004 08:38:50 -0700, Gord Dibben gorddibbATshawDOTca wrote:

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"
wrote:

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?

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 meg

Thanks, Gord

On Tue, 06 Jul 2004 13:34:36 -0500, Myrna Larson
wrote:

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

On Tue, 06 Jul 2004 08:42:40 -0700, Gord Dibben gorddibbATshawDOTca wrote:

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


On Tue, 06 Jul 2004 08:38:50 -0700, Gord Dibben gorddibbATshawDOTca wrote:

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"
wrote:

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?