CODEWITHSUNDEEP
excel
saif
saif

Reputation: 27

Remove a combination of Number and . from a string in Excel

I have the input "Nokia S40 Browser 2.2.0" from this i want to remove version number. The output should be "Nokia S40 Browser". In other words i want to remove 2.2.0 from my input. How can I do that?

Upvotes: 0

Views: 72

Answers (1)

Gordon
Gordon

Reputation: 1165

If the version number is always the last part of the text then you can use

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

To get the last part and 

=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)),"")

To remove the last part of the text.

Screenshot

Or you could use a RegEx to remove the version numbers

Function remomveVers(Myrange As Range) As String
    'Dim regEx As New regEx
    Set regEx = CreateObject("VBScript.RegExp")
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "([0-9]{1,2}[.][0-9]{1,2}[.][0-9]{1,2})|([0-9]{1,2}[.][0-9]{1,2})"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            remomveVers = regEx.Replace(strInput, strReplace)
        Else
            remomveVers = "Not matched"
        End If
    End If
End Function

You can use this function on your sheet (see screenshot below)

Secreenshot RegEx

Upvotes: 2

Related Questions