If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
String manipulation
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. |
#3
|
|||
|
|||
String manipulation
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. |
#4
|
|||
|
|||
String manipulation
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. |
#5
|
|||
|
|||
String manipulation
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? |
#6
|
|||
|
|||
String manipulation
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? |
#7
|
|||
|
|||
String manipulation
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? |
#8
|
|||
|
|||
String manipulation
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 |
#9
|
|||
|
|||
String manipulation
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? |
#10
|
|||
|
|||
String manipulation
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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
creating a string | Mike | Running & Setting Up Queries | 3 | June 16th, 2004 08:15 PM |
Clip string contents for comparison | Tyler Smith | Running & Setting Up Queries | 2 | June 16th, 2004 04:09 PM |
Question re MailMerge and VB.NET | thecoiman | Mailmerge | 5 | May 17th, 2004 04:13 PM |
Inserting a space into a text string | Brian Anderson | Worksheet Functions | 1 | April 6th, 2004 05:39 AM |