Search a list of strings in a column where each cell contains multiple values

Question

I have a column where each cell has multiple strings divided by a "/". I have as well a list of strings.

I want to search for each item of the list in the column and each time that I find an item write in another column with the addition of the Shortname next to it.

Each one of those columns are in different worksheets.

Something like the image below:

The first column is where I want to search, the second is what I want to search, and the third is the resulting column.
For every string that starts with "GPRF_", I want to write it in the third column with the Shortname associated to it.
In this example "GPRF_TxChPower" appears 3 times, so it is written 3 times with each Shortname associated to it before passing to the next item.

Example

For now, I used this line:

IF(ISNUMBER(SEARCH(G35;TestConfigs!$B$3&"|||"&TestConfigs!$B$4&"|||"&TestConfigs!$B$5&"|||"&TestConfigs!$B$6&"|||"&TestConfigs!$B$7&"|||"&TestConfigs!$B$8&"|||"&TestConfigs!$B$9&"|||"&TestConfigs!$B$10&"|||"&TestConfigs!$B$11));1;0)

That basically searches what I want(G35), which is in the worksheet Commun, in the column(B3:11) on the worksheet TestConfigs, if it is found returns 1 else 0 to the cell (F35), also in the worksheet Commun.

And then in the column I want the values to be written I did:

=IF(Commun!F35=1;Commun!G35;"")

If the result in F35 is 1, I write what is written in G35, else "".

Answer 1

Please, try the next code. It uses only two sheets, processing against "GPRF_" prefix. Using arrays and working mostly in memory, it should be very fast. It assumes that the range to be processed has the headers on the second row and cells to be iterated starting from the third row. It returns in the Next sheet. It may return anywhere if you correctly Set sh2...

 Sub ExtractShortNameByPrefix()
   Dim sh1 As Worksheet, lastR As Long, sh2 As Worksheet, arr, arrCell
   Dim i As Long, j As Long, dict As Object
   
   Const strPref As String = "GPRF_" 'prefix to be searched for
   Const colToReturn As Long = 1    'column number where to return the processed array
   
   Set sh1 = ActiveSheet 'use here the sheet to be processed (your Worksheet1)
   Set sh2 = sh1.Next    'use here the sheet where to return (your Worksheet3)
   lastR = sh1.Range("A" & sh1.rows.count).End(xlUp).row 'last row
   arr = sh1.Range("A3:B" & lastR).Value2 'place the range in an array for faster iteration and processing
   
   Set dict = CreateObject("Scripting.Dictionary")
   
   For i = 1 To UBound(arr)
        If InStr(arr(i, 1), strPref) > 0 Then
            arrCell = Split(arr(i, 1), "/") 'split the string by "/" separator if prefix exists
            For j = 0 To UBound(arrCell)
                If left(arrCell(j), Len(strPref)) = strPref Then
                    dict(arrCell(j) & "_" & arr(i, 2)) = 1 'place in the dictionay as key UNIQUE concatenations...
                End If
            Next j
        End If
   Next i
   'drop the processed dictionary keys:
   With sh2.cells(2, colToReturn).Resize(dict.count, 1)
        .Value2 = Application.Transpose(dict.Keys)
        .cells(1, 1).Offset(-1).value = strPref
        .EntireColumn.AutoFit
   End With

You can change "strPrefix" and "columnToReturn" constants to process a different prefix and return in a different column...

Please, send some feedback after testing it.