CODEWITHSUNDEEP
regexvbaexcel
user3275602
user3275602

Reputation: 11

Excel - Extract all occurrences of a String Pattern + the subsequent 4 characters after the pattern match from a cell

I am struggling with a huge Excel sheet where I need to extract from a certain cell (A1), all occurrences of a string pattern e.g. "TCS" + the following 4 characters after the pattern match e.g. TCS1234 comma-separated into another cell (B1).

Example:

Cell A1 contains the following string:

HRS164, SRS3439(s), SRS3440(s), SRS3441(s), SRS3442(s), SRS3443(s), SRS3444(s), SRS3445(s), SRS3449(s), SRS3450(s), SRS3451(s), SRS3452(s), SYSBASE.SSS300(s), TCS3715(s), TCS3716(s), TCS3717(s), TCS4037(s), TCS1234

All TCS-Numbers shall be comma-separated in B1: 

TCS3715, TCS3716, TCS3717, TCS4037, TCS1234

It is not necessary to also extract the followed "(s)".

Could someone please help me (excel rookie) with this challenge?

TIA Erika

Upvotes: 1

Views: 2649

Answers (3)

István Hirsch
István Hirsch

Reputation: 122

If the TCS-parts are always at the end of the string as in your example, I would use (in B1):

=REPLACE(A1,1,FIND("TCS",A1)-1,"")

Upvotes: 0

Jim Simson
Jim Simson

Reputation: 2862

Here is what I would use for something like that: also a user defined function:

Function GetTCS(TheString)
    For Each TItem In Split(TheString, ", ")
        If Left(TItem, 3) = "TCS" Then GetTCS = GetTCS & TItem & " "
    Next
    GetTCS = Replace(Trim(GetTCS), " ", ", ")
End Function

This returns "TCS3715(s), TCS3716(s), TCS3717(s), TCS4037(s), TCS1234" out of your string. If you don't know how to create a user defined function, just ask, it's pretty straight forward and I'd be happy to show you. Hope this helps.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

Try the following User Defined Function:

Public Function Xtract(r As Range) As String
    Dim s As String, L As Long, U As Long
    Dim msg As String, i As Long
    s = Replace(r(1).Text, " ", "")
    ary = Split(s, ",")
    L = LBound(ary)
    U = UBound(ary)
    Xtract = ""
    msg = ""
    For i = L To U
        If Left(ary(i), 3) = "TCS" Then
            If msg = "" Then
                msg = Left(ary(i), 7)
            Else
                msg = msg & "," & Left(ary(i), 7)
            End If
        End If
    Next i
    Xtract = msg
End Function

Upvotes: 0

Related Questions