How to find variable length/position text string in cell

[hizzle]

Can't quite figure this out.

I have a list of part numbers (about 20 characters, containing numbers, letters, symbols and spaces) and what I am trying to do is look for certain "codes" within each part number and put them in a separate column. The code does not always appear at the same position in the part number, nor is it always the same length.

For example, I am trying to take the part numbers
213241KRT : purplewidget
2355434634QCK yellowpencil
43GRVRD : bluephone

And for each part number I want to look and see if the cell contains the code "KRT" or "QCKT" or "GRVRD", and if so then put whichever code is contained in the part number in the column next to it.
I'm guessing this requires some kind of macro, but I haven't been able to find anything that quite works. Any ideas would be very helpful. Thank you!

 

[barry houdini]

Are there only those 3 codes?

Book1
	A	B	C	D
1	213241KRT : purplewidget		KRT
2	2355434634QCK yellowpencil		QCK
3	43GRVRD : bluephone		GRVRD
4
Sheet4

Formula in C1 copied down column

=INDEX({"KRT","QCK","GRVRD"},MATCH(TRUE,INDEX(ISNUMBER(SEARCH({"KRT","QCK","GRVRD"},A1)),0),0))

 

[Oaktree]

Does the code always start in the first position after the last number and end in the position before the first space?

 

[Oaktree]

...if so (per my previous post), try:

=MID(A1,MATCH(10,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+1,SEARCH(" ",A1)-MATCH(10,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

confirmed with CTRL+SHIFT+ENTER and copied down

Book13
	A	B	C	D
1	213241KRT : purplewidget	KRT
2	2355434634QCK yellowpencil	QCK
3	43GRVRD : bluephone	GRVRD
Sheet1

which is an adaptation of Barry's formula from here:

http://www.mrexcel.com/board2/viewtopic.php?p=732194

 

[hayati]

Alternative

Hizzle,

This formula seems to work as well:

=IF(ISERROR(FIND("KRT",A6)),IF(ISERROR(FIND("QCK",A6)),"GRVRD","QCK"),"KRT")

Not too sure if it has any constraints though.

Cheers,
Matt

 

[Greg Truby]

hizzle,

How many unique SKUs' substrings are we talking about looking for here? By that I don't mean how many rows to scan, but how many codes like "KRT", "QCKT" and "GRVRD" need to be looked for? Just these three; just a dozen or so; or hundreds; or thousands?

 

[fairwinds]

Hi,

Maybe another option:

=LOOKUP(9.99999999999999E+307,FIND({"","KRT","QCK","GRVRD"},A1),({"","KRT","QCK","GRVRD"}))

 

[hizzle]

Thanks everyone!

In this case the "code" is not necessarily next to a space (or any particular character).
And in general I would be searching from thousands of product numbers. The number of codes I would be searching for would likely be around 10 or so.

I was trying to think about a way that was most flexible so I could easily add more "codes" to look for as the product numbers continue to grow by the hundreds...
Thanks!!

 

[Scott Huish]

Just for clarification is the code and the description in the same cell?

 

[barry houdini]

If you adapt Fairwind's formula as below then you can add codes as necessary in column E without changing the formula. Note that the column header in E1 and blank space in E2 must remain.

Also, you might have a problem if any of your codes is a subset of another e.g. ABC and ABCD

Book1
	A	B	C	D	E	F
1	Part Number		Code		Codelist
2	213241KRT : purplewidget		KRT
3	2355434634QCK yellowpencil		QCK		KRT
4	43GRVRD : bluephone		GRVRD		QCK
5	43BARRY : bluephone		BARRY		GRVRD
6					BARRY
7
Sheet4

Formula in C2 copied down column

=LOOKUP(9.99999999999999E+307,FIND(OFFSET($E$2,,,COUNTA(E:E),),A2),OFFSET($E$2,,,COUNTA(E:E),))