Remove Non_Numeric from string

[JonRowland]

Hi,

The following code will strip numbers from a string however I would like the opposite and like to strip all but numbers from a string which is then placed into a variable (in this case celltext.

Not IsNumeric doesn't work and IsText doesn't seem to work. My question is is there an opposite of IsNumeric?

        While IsNumeric(Right(celltext, 1))
            celltext = Left(celltext, Len(celltext) - 1)
        Wend
        'Write the new text string back into the cell
        Selection.Value = celltext

Thanks Jon

 

[Rick Rothstein]

Try this...

For X = 1 To Len(celltext)
  If Mid(celltext, X, 1) Like "[!0-9]" Then Mid(celltext, X) = " "
Next
Selection.Value = Application.Trim(celltext)

 

[Peter_SSs]

If you wanted to replace them all at once:

Dim RX As Object

Set RX = CreateObject("VBScript.RegExp")
RX.Global = True
RX.Pattern = "\D"
celltext = RX.Replace(celltext, "")

 

[Peter_SSs]

Another one-pass way

celltext = Evaluate(Replace("concat(if(isnumber(mid(#,sequence(len(#)),1)+0),mid(#,sequence(len(#)),1),""""))", "#", """" & celltext & """"))

 

[Rick Rothstein]

Another one-pass way

celltext = Evaluate(Replace("concat(if(isnumber(mid(#,sequence(len(#)),1)+0),mid(#,sequence(len(#)),1),""""))", "#", """" & celltext & """"))

For those of you who do not have a version of Excel with the CONCAT or SEQUENCE functions in them, you can use this modified version of Peter's code line to do the same thing...

celltext = Join(Evaluate(Replace("TRANSPOSE(IF(ISNUMBER(MID(""#"",ROW(1:99),1)+0),MID(""#"",ROW(1:99),1),""""))", "#", celltext)), "")

NOTE: I have assumed the text in the variable 'celltext' will not be longer than 99 characters in length.

 

[JonRowland]

Rick and Peter, thanks guys you've given me more than one solution which is great. I think as I want to do the replace all at once Peter' code works for me. How

VBA Code:
Dim RX As Object

Set RX = CreateObject("VBScript.RegExp")
RX.Global = True
RX.Pattern = "\D"
celltext = RX.Replace(celltext, "")

Thanks
Jon

 

[Peter_SSs]

You're welcome. Glad we could help.