Removing a number sequence from product name?

Question

I have rug names and I'm trying to remove the part that has (almost always) numbers like this:

Adrienne 3729F Beige/Blue Geometric Area Rug 
Adrienne 3836E Grey/Multi Geometric Area Rug 
Adrienne 3960G Navy/Gray Floral Area Rug 
Adrienne 4130C Brown/Orange Floral Area Rug 
Adrienne 4138A Multi Stripe Area Rug 
Adrienne 4142A Grey/Brown Floral Area Rug 
Adrienne 4145E Green/Plum Oriental Area Rug

To clarify, the part I'm trying to remove is after Adrienne however there are other collection names. This just a snap shot of what I'm dealing with. I've tried everyting and asked everyone and no one could help.

Use the `Split` function to split it by spaces, then check the 2nd element for a numeric first letter, and then reassemble the string omitting the 2nd element. — braX, Dec 18 '17 at 17:30
Is the length of the part you're trying to remove always the same and always separated by a space " " and always in the same place? And could you perhaps show some of the attempts you've made? — SilentRevolution, Dec 18 '17 at 17:31

Ibo · Answer 1 · 2017-12-18T18:46:04.820

2

This is rather advanced, but it is more powerful to use Regular expressions. Define your range that you want to remove the numbers. It could be one cell or numerous cells=range at once. You need to define your range in sub Main and it will take care of that. As an example, I used range("A1:A3"). If your numbers are in column A, you can simply use cells(1,1).CurrentRegion

Sub RegexReplace(rng As Range, ByVal replace_what As String, ByVal replace_with As String)
    Dim RE As Object
    Dim v
    Dim i As Long
    Set RE = CreateObject("vbscript.regexp")
    v = rng.Value2
    RE.Pattern = replace_what
    RE.Global = True
    With RE
        For i = 1 To UBound(v)
            v(i, 1) = .Replace(v(i, 1), replace_with)
        Next
    End With
    rng = v
End Sub

Sub Main()
    Call RegexReplace(Range("A1:A3"), "\d", "")
End Sub

Result:

Edit 1:

If you ever wanted to also remove the characters attached to the numbers, you can easily change the regex parameter in the function and achieve it:

Sub Main()
    Call RegexReplace(Range("A1:A7"), "(\d+)[A-Z]", "")
End Sub

Result:

edited Dec 18 '17 at 18:46

answered Dec 18 '17 at 17:50

Ibo

4,081
6
45
65

1

`Call` keyword is obsolete, has been for at least 20 years. – Mathieu Guindon Dec 18 '17 at 18:15
I know it would work without that too, but it is just how I learned and the minimum I can tell is that I have encountered cases that the sub did not work without `call`. Plus, `call` for me shows that the sub is a sub and not a function and it is a user-defined method. There are some other uses such as calling a class once which `call` will be useful to do that on one line or when converting to VB.NET etc it will help the conversion – Ibo Dec 18 '17 at 18:26
I'd love to see that sub that didn't work well without `Call` - the single use-case I know that "justifies" a `Call` keyword involves instructions separators `:` that shouldn't even be there in the first place, making the first (parameterless) method call be interpreted as a line label. FWIW writing proper OOP code will go a much longer way towards facilitating .NET conversion than using keywords deprecated 3 versions prior to VBA7. – Mathieu Guindon Dec 18 '17 at 18:30
well, I am not going to break my head to find the case that did not work properly without the `call` keyword, but search on SO and you will see how people mention examples that using `call` is useful. – Ibo Dec 18 '17 at 18:44
[This](https://stackoverflow.com/questions/2573597/should-i-use-call-keyword-in-vb-vba#comment47737146_22325284) is the case you're looking for. – Mathieu Guindon Dec 18 '17 at 18:55
I agree with you. I never liked `:` separation, but there are more cases. One case if that when you call a function that has some optional arguments most likely it will not work in the Immediate window unless you provide the optional arguments, but if you use the `call` keyword, it will work. Also [check this](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/call-statement) and [this](https://stackoverflow.com/questions/2573597/should-i-use-call-keyword-in-vb-vba) – Ibo Dec 18 '17 at 19:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161474/discussion-between-mats-mug-and-ibo). – Mathieu Guindon Dec 18 '17 at 19:08

score 0 · Answer 2 · answered Dec 18 '17 at 17:43

0

Are those in different columns or is it one big string?

A simple way might be to simply replace every single number like so for the number 3:

=replace(A1;find("3";A1);1;"")

answered Dec 18 '17 at 17:43

Alexander

93
1
6

And what will that do to `Adrienne 4130C Brown/Orange Floral Area Rug`? – Mathieu Guindon Dec 18 '17 at 18:38

score 0 · Answer 3 · answered Dec 18 '17 at 17:48

If you know how to use RegEx and VBA, this is a good solution:

Option Explicit

Public Function RemoveSomeNumbers(s As String) As String

    Dim regEx           As Object
    Dim inputMatches    As Object
    Dim regExString     As String

    Set regEx = CreateObject("VBScript.RegExp")

    With regEx
        'from 3 to 4 digits grouped together:
        .Pattern = "[\d]{3,4}.*$"
        .IgnoreCase = True
        .Global = True

        Set inputMatches = .Execute(s)

        If regEx.test(s) Then
            RemoveSomeNumbers = .Replace(s, vbNullString)
        End If

    End With

End Function

Public Sub TestMe()
    Debug.Print RemoveSomeNumbers("Adrienne 3729F Beige/Blue Geometric Area Rug")
    Debug.Print RemoveSomeNumbers("Adrienne 4142A Grey/Brown Floral Area Rug")
    Debug.Print RemoveSomeNumbers("Vityata 4142A Grey/Brown Floral Area Rug")
End Sub

It will remove anything after the first 3 or more digits in the sentence, which are found. It is better than the Split() because the product name may include a space. If no space is included by default, an easier solution is simply to Split() and to take the first result:

Split("Adrienne 3729F Beige/Blue Geometric Area Rug")(0)

Removing a number sequence from product name?

3 Answers3