File ID here 743902.
Your summary of the problem is almost right
"So to understand this correctly, you want to lookup multiple values in the same cell and then return another column such as price or something if both sequences are somewhere in the first collum?
Only I don't wnat to return price, I want to return a concatenation of the 2 values found in the first column.
I have 2 lists of products. One list is correct with 10 digit codes (1st 5 digits indicate manufacturer, 2nd 5 digits indicate unique products - commonly known as UPC codes found on bar codes)
The 2nd list contains incorrect codes that have anywhere from 11-14 digits. Somewhere in those codes are the same 10 sequential digits - maybe at the front, maybe at the back or the middle.
I need a formula to extract the 10 digits, in the 2nd list either as one 10 digit number or 2 x 5 digits, whichever is easier to write a formula for. The 2nd list has unique information not found in the 1st.
The destination of the data in the 2nd list is software that will only recognise a 10 digit number relating to a product.
That's all the detail I can give