0

I have in Column AJ a lot of text strings (categories separated by ;#) example is Cell AJ2 has (Accounting: and some text;#Business: and some text) in each row. I want to delete specific string of categories and anything that follows it in column AJ that starts with "Business:" or ":#Business:

enter image description here

Community
  • 1
  • 1
cookiemonster
  • 368
  • 1
  • 8
  • 22
  • search for "vba excel regex" ... this is the top hit https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#22542835 – jsotola Aug 05 '17 at 01:22

1 Answers1

2

Try this code.

Sub test()
    Dim vDB, vSplit, s As String
    Dim n As Long, i As Long
    vDB = Range("aj2", Range("aj" & Rows.Count).End(xlUp))
    n = UBound(vDB, 1)
    For i = 1 To n
        s = vDB(i, 1)
        If InStr(s, "Business") Then
            vSplit = Split(s, "Business")
            s = Replace(s, "Business" & vSplit(UBound(vSplit)), "")
            If Right(s, 1) = "#" Then
                s = Left(s, Len(s) - 2)
            End If
            vDB(i, 1) = s
        End If
    Next i
    Range("ak2").Resize(n) = vDB
End Sub
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14
  • If I want to delete everything before Business. – cookiemonster Aug 05 '17 at 02:33
  • 1
    Dim vDB, vSplit, s As String Dim n As Long, i As Long vDB = Range("aj2", Range("aj" & Rows.Count).End(xlUp)) n = UBound(vDB, 1) For i = 1 To n s = vDB(i, 1) If InStr(s, "Business") Then vSplit = Split(s, "Business") vDB(i, 1) = "Business" & vSplit(UBound(vSplit)) Else vDB(i, 1) = Empty End If Next i Range("ak2").Resize(n) = vDB – Dy.Lee Aug 05 '17 at 02:38
  • I added more sample data to delete everything before business. I updated the post with a sample image. I highlighted a row.. There are two businesses, so when I run the code, in the comment, to delete everything before business, it works perfectly but it only shows one Business for the highlighted row. – cookiemonster Aug 05 '17 at 03:20
  • 1
    Sub test3() Dim vDB, vSplit, s As String Dim n As Long, i As Long, k As Integer vDB = Range("aj2", Range("aj" & Rows.Count).End(xlUp)) n = UBound(vDB, 1) For i = 1 To n s = vDB(i, 1) If InStr(s, "Business") Then k = InStr(s, "Business") - 1 vDB(i, 1) = Right(s, Len(s) - k) Else vDB(i, 1) = Empty End If Next i Range("ak2").Resize(n) = vDB End Sub – Dy.Lee Aug 05 '17 at 04:04