# How to find variable length/position text string in cell

#### hizzle

##### New Member
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!

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### barry houdini

##### MrExcel MVP
Are there only those 3 codes?
Book1
ABCD
1213241KRT : purplewidgetKRT
22355434634QCK yellowpencilQCK
343GRVRD : bluephoneGRVRD
4
Sheet4

Formula in C1 copied down column

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

#### Oaktree

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

#### Oaktree

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

Code:
=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
ABCD
1213241KRT : purplewidgetKRT
22355434634QCK yellowpencilQCK
343GRVRD : bluephoneGRVRD
Sheet1

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

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

#### hayati

##### Board Regular

Alternative

Hizzle,

This formula seems to work as well:
Code:
=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

##### MrExcel MVP
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

##### MrExcel MVP

Hi,

Maybe another option:

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

#### hizzle

##### New Member
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

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

#### barry houdini

##### MrExcel MVP
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
ABCDEF
1Part NumberCodeCodelist
2213241KRT : purplewidgetKRT
32355434634QCK yellowpencilQCKKRT
443GRVRD : bluephoneGRVRDQCK
543BARRY : bluephoneBARRYGRVRD
6BARRY
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),))

Replies
4
Views
212
Replies
4
Views
75
Replies
5
Views
109
Replies
6
Views
153
Replies
0
Views
211