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! :)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
8,053
Office Version
  1. 365
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
8,053
Office Version
  1. 365
...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,022
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),))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,002
Messages
5,834,821
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top