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

Question

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

Here is something simular: http://stackoverflow.com/questions/8146485/returning-a-regex-match-in-vba-excel — Daniel Dušek, Feb 05 '14 at 15:13

Jim Simson · Answer 1 · 2014-02-05T15:52:11.333

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.

score 0 · Answer 2 · answered Feb 05 '14 at 15:00

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

score 0 · Answer 3 · answered Feb 05 '14 at 15:52

0

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,"")

answered Feb 05 '14 at 15:52

István Hirsch

122
3

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

3 Answers3