Excel extracting an exact string from cells with random structure

Question

Ok so,

In column A basically every cell has a different composition and doesn't have the same string Before And After the value we are looking to extract. For Example:

ODODODODEFGH4OGOGOG

LALALALALABCDE12-1ALALALALA

IRIRIRIRIJKLMNOROROR

And I need to extract the following strings which are located in another sheet ((its an SKU information combining text and numbers with variable length) from column A and list it in the column B next to it

ABCDE12-1

EFGH4

IJKLMN

I've tried Find, Mid, Lookup, Index functions but can't seem to find the solution. Any help very appreciated!

What's the criteria to find the SKU? What I mean is how would the code identify `EFGH4` in `ODODODODEFGH4OGOGOG`. What is the criteria? It like finding `Needle` in `Haystack` `HAYNEEDLESTACK` :| — Siddharth Rout, Dec 11 '13 at 03:43
Unless you can explain in words what distinguishes the bit you want from the bit you don't want, nobody can help you with your solution. Is it "the bit of string that is not two repeating characters at the beginning or end"? Even that's not true since you have the `A` in `ABC` after `LALALALAL` ... — Floris, Dec 11 '13 at 03:47
I see no pattern in figuring out the SKU's that you show as results from the original string. If the ABCDE12-1 is a typo, and should really be BCDE12-1, then we can figure out a pattern. Without a discernible and describeable pattern, as others have written, the only other solution is for you to have a separate list of valid SKU's that one could use as a lookup table. — Ron Rosenfeld, Dec 11 '13 at 04:38
@RonRosenfeld Of course I have the values I need to extract, sorry if this wasn't clear, they are in another sheet. Now I just need to remove all the text from every cell in column A that is arround the string I want to isolate. — gimme_donuts, Dec 11 '13 at 04:42
@SiddharthRout the other sheet is one string per cell: ABCDE12-1, EFGH4, IJKLMN — gimme_donuts, Dec 11 '13 at 04:46

score 1 · Accepted Answer · answered Dec 11 '13 at 05:01

1

Let's say your Sheet1 and Sheet2 looks like this.

enter image description here

Put this formula in Cell B1 of Sheet1 and pull it down.

=IF(SUMPRODUCT(COUNTIF(A1,"*"&Sheet2!$A$1:$A$3&"*")),INDEX(Sheet2!A:A,SUMPRODUCT(COUNTIF(A1,"*"&Sheet2!$A$1:$A$3&"*")*ROW(Sheet2!$A$1:$A$3))),"")

enter image description here

answered Dec 11 '13 at 05:01

Siddharth Rout

147,039
17
206
250

hey thanks a lot, this works great, but as soon as I change $A$3 to a bigger value than $A$3000 it doesn't work anymore， it returns the value 0 ( I haven't determined the exact value from which it doesn't work, but I know that up to around 3000 it works and if I input for example $A$4000 it doesn't work anymore. As I have close to 5000 values, maybe there is a tweak? If there is no work arround I guess I can split the list in two. Thanks！！ – gimme_donuts Dec 11 '13 at 05:33
What is the exact formula that you are using for 4000 rows? – Siddharth Rout Dec 11 '13 at 05:34
=IF(SUMPRODUCT(COUNTIF(A1,"*"&Sheet2!$A$1:$A$4000&"*")),INDEX(Sheet2!A:A,SUMPRODUCT(COUNTIF(A1,"*"&Sheet2!$A$1:$A$4000&"*")*ROW(Sheet2!$A$1:$A$4000))),"") – gimme_donuts Dec 11 '13 at 05:35
One moment testing it – Siddharth Rout Dec 11 '13 at 05:36
It is working for me. I tried this `=IF(SUMPRODUCT(COUNTIF(A1,"*"&Sheet2!$A$1:$A$4000&"*")),INDEX(Sheet2!A:A,SUMPRODUCT(COUNTIF(A1,"*"&Sheet2!$A$1:$A$4000&"*")*ROW(Sheet2!$A$1:$A$4000))),"")` – Siddharth Rout Dec 11 '13 at 05:38
Ensure that there are no duplicate in the list in Sheet2 – Siddharth Rout Dec 11 '13 at 05:39
Yeah there were some duplicates. Now that you mention it, I have noticed that there are some strings that are very similar and the shortest might be recognised several times. For example there is B1320 and CPB1320. I guess this is causing other problems. that's fine, it already helps a lot! Thanks again for the quick help! – gimme_donuts Dec 11 '13 at 05:52

score 0 · Answer 2 · answered Dec 11 '13 at 13:05

OK, now that we know you have a lookup table, set up the following:

On Some sheet, list your valid SKU's in a vertical Named Range. e.g: ValidSKU refers to: Sheet2!$A$2:$A$100

Then with your gibberish string on some sheet in A1, to return the valid SKU from the string:

B1:  =INDEX(ValidSKU,LOOKUP(9E+307,FIND(ValidSKU,A1),ROW(INDIRECT("1:10000"))))

The "10000" argument in the above formula needs to be a number that is at least as large as the number of SKU's in your list. So if you have 5000 valid SKU's, use some number greater than that.

Then fill down as far as needed.

This method has a weakness: If there are overlapping SKU's, it will return the lowest one that matches. So it would be best to have your longest SKU's at the bottom of the list.

In other words, if you have two SKU's ABCDE12-1, and ABCDE12, both of those are found in your 2nd string. Whichever is located last in the ValidSKU list will be the one returned. I don't know of any way (other than position) to differentiate these two possibilities.

Somehow this always returns 0. The length of the string is actually easy to sort with the function LEN . — gimme_donuts, Dec 12 '13 at 07:43
The only way I can get zeros as a result is if ValidSKU refers to an empty range. I will try to edit my answer to show images — Ron Rosenfeld, Dec 12 '13 at 12:25
Cannot add the image now. Suggest you copy and paste an exact copy of the formula you are using, as well as the "Refers To" for validSKU, and ensure there is valid data in that range. — Ron Rosenfeld, Dec 12 '13 at 13:14

Excel extracting an exact string from cells with random structure

2 Answers2