Strip out numbers from text

[JEngles]

Hi All, just wondering if you can help me out.

Basically this is what I have so far.

Sub StripOutVersionNum()

Count = 2
Sheets("Sheet2").Select

Do While Range("A" & Count) <> ""
    StringName = Range("A" & Count).Value
    FindDot = InStr(1, StringName, ".",)
    If FindDot > 0 Then
        NewStringFirstWord = (Left(StringName, (InStr(FindDot - 2, StringName, " "))))
        NewStringSecondWord = (Mid(StringName, (InStr(FindDot, StringName, " ") + 1), Len(StringName)))
        Range("B" & Count).Value = NewStringFirstWord & NewStringSecondWord
    End If
    Count = Count + 1
Loop
End Sub

Some sample text:

@RISK 4.5 for Excel
@RISK 5.5 Industrial for Excel
@RISK 5.5 Standard for Excel
@RISK for Excel, Standard Network Edition
@RISK Standard 5.5.1

Now while my script does strip out the version number if they are in the middle it doesn't work if they are at the end. Also I can't figure out what if the number is stupidly at the start...grrr.

Thanks in advance,
John

 

[mintjulep]

As a Function

Public Function Strip_Num(Feed As String)

Dim count As Integer
Dim char, New_String As String

For count = 1 To Len(Feed)
char = Mid(Feed, count, 1)
If Asc(char) < 46 Or Asc(char) = 47 Or Asc(char) > 57 Then New_String = New_String & char
Next count
Strip_Num = Trim(New_String)

End Function

 

[JEngles]

Unfortunately It's not all the numbers I'm looking to remove, just the version numbers. e.g 4.5, 5.5.1.

I can appreciate that it's a lot more complicated.

 

[combo]

Are there any rules for version position? Otherwise the only solution is to remove all numbers. What to remove in:
'@RISK 5.5.1 Standard 5.5'?



combo

 

[SkipVought]

How about

Function StripOutVersionNum(s As String) As String
    Dim i As Integer, j As Integer, a, b, bStripOutVersionNum As Boolean
    
    a = Split(s, " ")
    
    For i = 0 To UBound(a)
        b = Split(a(i), ".")
        
        bStripOutVersionNum = True
        
        For j = 0 To UBound(b)
            If Not IsNumeric(b(j)) Then
                bStripOutVersionNum = False
            End If
        Next
        
        If bStripOutVersionNum Then StripOutVersionNum = a(i)
    Next
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

Assuming from your initial post that the rule for identifying a version number is a dot, and trying to copy your method as closely as possible, then something like the following should work:

[blue]Sub StripOutVersionNum()

    Count = 2
    Sheets("Sheet2").Select
    
    With CreateObject("vbscript.regexp")
        .Pattern = "\d+[\.\d]+ *"
        .Global = True
        
        Do While Range("A" & Count) <> ""
            Range("B" & Count).Value = Trim(.Replace(Range("A" & Count).Value, ""))
            Count = Count + 1
        Loop
    End With
    
End Sub[/blue]