Searching for a string in an array of strings

[myk_r]

Hi,

I'm having a little trouble with this one. What I'm trying to do is to use Sheet1 as a reference sheet, then Sheet2 as an order sheet.

Sheet2 has all the orders, but they are all strings, with the item code in them. What I want to do is extract the code numbers (potentially hundreds of numbers), then compare them against the reference sheet (Sheet1), again potentially hundreds of numbers. The item code may be anywhere in the string.

What I want returned is the Item name based on the reference sheet.

So basically:
1. Get item code from Sheet2
2. Compare item code in Sheet1
3. Return item name from Sheet1. Place it in Sheet2

Is there any way to do this as a formula in a cell?

[dredwolf]

Is the Item Code always the same 'Size' for example, in your sample it is 1 digit,followed by 1 letter, and separated from the rest by a space...is this consistent ?

[dredwolf]

Also..is item code always at the end?, if so, it will make solution easier

[myk_r]

Hi dredwolf,

No, unfortunately the item code may not always have a space between the item, it is not always at the end of the string as there may be comments after the item code, and it's not consistent in size.

I've had limited success with the SEARCH function, but all that I got is where in the Order name the Code# is.

So from Apl 1A, using the search function, I got a 5.

[dredwolf]

Which is right...the code starts at position 5 in the string, using MID() this could work...the REAL problem is, you have no way to define code size or position, it may not be separated,...Have notes at least got some consistency, so we can get rid of them before we look for the code?...with your sample it is easy, but, from your answer to my question, it's not going to be that simple