How to find variable length/position text string in cell

hizzle

New Member
Joined
Apr 30, 2004
Messages
26
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! :)
 

Some videos you may like

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jun 20, 2002
Messages
7,988
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
Joined
Jun 20, 2002
Messages
7,988
...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 (y) from here:

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

hayati

Board Regular
Joined
Oct 3, 2005
Messages
118

ADVERTISEMENT

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
Joined
Jun 19, 2002
Messages
10,014
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
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi,

Maybe another option:

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

hizzle

New Member
Joined
Apr 30, 2004
Messages
26
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!! :LOL:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Just for clarification is the code and the description in the same cell?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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),))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,386
Messages
5,571,829
Members
412,421
Latest member
grace_abar
Top