• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 51
  • Last Modified:

delete everything from the first occurrence of a number till the end of the cell using VBA

Dear Experts:

For the selected cells I would like to run  a macro that performs the following action:

Search for the occurrence of the first number and then delete everything from the first occurrence till the end of the cell

Before:
Mouth Gag, Roser-Koenig, 16 Cm, Sinus Lift
Mouth Gag, Roser-Koenig, 19 Cm Lower
Chest Support for Mouth Gag 38-198-00

After:
Mouth Gag, Roser-Koenig,
Mouth Gag, Roser-Koenig,
Chest Support for Mouth Gag

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Andreas Hermle
Asked:
Andreas Hermle
2 Solutions
 
NorieVBA ExpertCommented:
Andreas

Where is the data located?

If the data you posted was in A1:A3 you could try this.
Dim cl As Range
Dim rng As Range
Dim regexp As Object
Dim mat As Object

    Set regexp = CreateObject("vbscript.regexp")
 
    regexp.Pattern = "[0-9]"
    
    Set rng = Range("A1:A3")
    
    For Each cl In rng.Cells
         Set mat = regexp.Execute(cl.Value)
         If mat.Count = 1 Then
            cl.Offset(, 1).Value = Left(cl.Value, mat(0).firstindex)
         End If
    Next cl

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try to eliminate the last space as well
Sub CellRegexSimple()
Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = False
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "(.*?)\s?\d+"
    End With
    
    For Each c In Selection
        Set mtches = Nothing
        Set mtches = regex.Execute(c.Text)
        If Not mtches Is Nothing Then
            c.Formula = mtches(0).submatches(0)
        End If
    Next
Set regex = Nothing
End Sub

Open in new window

REgards
1
 
Rgonzo1971Commented:
and if you want to delete the last comma as well then use
.Pattern = "(.*?),?\s?\d+"

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Saqib Husain, SyedEngineerCommented:
Without VBA you can try this ARRAY formula

=LEFT(A2,MIN(IF(ISNUMBER(VALUE(MID(A2,ROW(OFFSET(A1,0,0,LEN(A2))),1))),ROW(OFFSET(A1,0,0,LEN(A2))),99999))-1)
2
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It's always a good practice to test if the string contains the desired pattern in it before actually manipulating the string so that if the string doesn't contain the set pattern, it won't throw an error.
 
You may try it like this...
Sub DeleteStringPart()
Dim RE As Object
Dim cell As Range

Set RE = CreateObject("VBScript.RegExp")
    With RE
        .Global = False
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "(.*?)\s?\d+"
    End With
    
    For Each cell In Selection
        If RE.test(cell.Value) Then
            cell.Value = RE.Execute(cell.Value)(0).submatches(0)
        End If
    Next cell
Set RE = Nothing
End Sub

Open in new window

0
 
Rgonzo1971Commented:
corrected code
Sub CellRegexSimple()
Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = False
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "(.*?)\s?\d+"
    End With
    
    For Each c In Selection
        Set mtches = regex.Execute(c.Text)
        If mtches.Count > 0 Then
            c.Formula = mtches(0).submatches(0)
        End If
    Next
Set regex = Nothing
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Dear both, your codes work best for me. Thank you very much for your great help. I really appreciiate it.

To all the others. Thank you very much for your swift and professional help.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Andreas! Glad we could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now