Extracting text string from cell with no delimiters

Hi

I am looking to extract non-delimited values from a cell.

In my attached test data

Column A is the product code
Column C is the non-delimited product codes in a customer order
Column E through H+ is how I would like the data extraced and displayed.

Out system exports order data showing all ordered products in the same cell with no delimiter.

I am hoping to find a way to search in Column A for a value and if found in Column C it should add that value to a new adjacent cell, for each matched value.

Regards

Hi

Apologies, maybe my example data isn't comprehensive enough.

The "product code" examples are set values. I am hoping that that set value can be searched for within the non-delimited strings in column C in order to extract that value and add them to separate adjacent cells.

I had a similar enquiry last year that was solved, however the logic was a little different so the solution formula from that one doesn't quite work.

Let me know if that explains it enough.

Regards

Please tell us the rules for the extraction columns E thru H.
They're of varying lengths; for starters, and how is Excel to know what to look for, and what to report for each column. Need to understand the logic.
Thanks.

Pete

For some further clarification

(the only reason i put the column in A, C and E was for clarity. The actual spreadhseet will have them adjacent)

The full product list will be in column A (2600+ products)
Every product code will be unique

The orders list in Column C is made up of anywhere from 1 - 10 products all in the same cell with no delimiter.

I can't do anything with a cell with 10 product codes all touching with no delimiter.

I am looking for a way to look in each cell in column C then search Column A to see if a matched value is found.

If a matched value is found it should be extract and put in its own cell.

When I said E through H, i meant that depending on the number of matched values in Column C, there may be 1 new column created, or there may be 10.

I hope that clarifies a little more.

Regards

Yes, this is how I am hoping to display the extracted data.

Originally Posted by sandy666

based only on the first post

PC.1	PC.2	PC.3	PC.4
abcde	jklmn	ww
ww	pppp	bmbm	aabbcc
ww
abcde	ww	bmbm
jklmn	ww	pppp	bmbm

is that what you want?
verify your expected result

Yes, this is how I am hoping to display the extracted data.

Does Power Query require advanced knowledge to use?

I have never used it before

In E3, how does the formula know to return ww?
In E4, how does the formula know to return abcde?

and so forth for all the columns.
Thx

My apologies, I tried to give sample data so as not to provide actual data as I thought it may be against the terms, but I don't mind if it will explain better.

Please see revised attached document.

how about post#6 ?

try to learn from here: Power Query

Power Query isn't harder than formulas, it's just a different way of thinking

Originally Posted by 6StringJazzer

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

I have crossposted to this link - https://www.mrexcel.com/board/thread.../#post-6023134

Originally Posted by JohnTopley

Sub extract()
Dim a, b
Dim i As Integer, j As Integer, n As Integer

a = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
b = Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)

ReDim c(1 To UBound(b, 1), 1 To 50)

Application.ScreenUpdating = False

For i = 1 To UBound(b, 1)
     n = 1
    
     c(i, 1) = i                    ' Order number
     
     For j = 1 To UBound(a, 1)
        If InStr(1, b(i, 1), a(j, 1)) Then  ' Match Product
            n = n + 1
            c(i, n) = a(j, 1)
        End If
    Next j
    
Next i

[H2].Resize(100, 50).ClearContents
[H2].Resize(UBound(c, 1), UBound(c, 2)) = c
    
Application.ScreenUpdating = True

End Sub

Is that a power query solution?