CODEWITHSUNDEEP
excelexcel-formula
nadr
nadr

Reputation: 17

Excel: Delete Last Word In String IF it is a number

Sorry if this has been asked before, but I can't find any answers anywhere that do what I need... :(

Basically I have a bunch of product titles, some with a number (SKU) at the end, some with not.

All SKUS will end with a number. In Pseudo: IF the last character of the last word is a number, remove the last word. - This isn't actually a word but a number.

Here is an example: "Medium Racer Back Top And Short Lounge Set 7091" (I want to delete the bold part if the last character is a number)

As always, thanks in advance for any help :)

D

Upvotes: 0

Views: 218

Answers (1)

QHarr
QHarr

Reputation: 84465

You could use a regex to replace the end numbers. Use pattern "\s\d+$" to get rid of white space at end.

Option Explicit

Public Sub test()

    Debug.Print GetString("Medium Racer Back Top And Short Lounge Set 7091")

End Sub

Public Function GetString(ByVal inputString As String) As Variant
    With CreateObject("vbscript.regexp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "[^\s]\d+$"    '<==  \s\d+$   '<==to remove white space at end as well
        If .test(inputString) Then
            GetString = .Replace(inputString, vbNullString)
        Else
            GetString = inputString
        End If
    End With
End Function

Used as User Defined Function in sheet:

enter image description here

Regex:

Try it

[^\s]\d+$

Match a single character not present in the list below [^\s]

\s matches any whitespace character (equal to [\r\n\t\f\v ])

\d+ matches a digit (equal to [0-9])

+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

Upvotes: 1

Related Questions