that's my first post here, sorry for any mistakes.

I have a column in excel where each cell has multiple strings divided by a "/". And I have as well a list of strings. What I want to do is search for each item of the list in the column and each time that I find an item it will be writen in another column with the addition of the Shortname next to it.

The other problem is that each one of those columns are in different worksheets.

Something like the image bellow:

The first column is where I want to search, the second is what I want to search, and the third is the resulting column. So, 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 what I did is, 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 "".

Thank you for your help.

If it lacks information, I will add more.

EDIT

Thanks to @FaneDuru for the image correction and the code. His answer solved my problem, thank you !

CodePudding user response：

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.

CodePudding user response：

I did not get you question right, but if you want to find how many times one text is repeated in other text you can use this:

=(LEN(B3)-LEN(SUBSTITUTE(B3,B4,"")))/LEN(B4)

where B3 is long text, and B4 is text to search.