Text & Number String Extraction Without VBA

[jawnathin]

Hello all,

Long-time reader, first-time poster. I need to find and extract a string from a text field that consists of two letters followed by three numbers (XX000) then nest that inside a VLOOKUP to return another corresponding cell of data. There are hundreds of rows of data that need this applied to them Sample data below:

Created	Comment	Desired Extraction
1/1/2013	Bananas BA426 Them more text	BA426
5/1/2013	Apples AP654 More additional text	AP654
6/3/2013	More Fruit MF055 Even more text	MF055
7/5/2013	More Text Before Numbers MT323 and this one is going to be really long to prove a point.	MT323

<tbody>
</tbody>

The VLOOKUP with the XX000 string is easy; pulling that string using a formula and no VBA is the hard part. Someone please tell me there's an easy way that I'm simply overlooking!

Edit: I just discovered that I may also have a master list of the strings that are embedded in the Comment column. Sample below:

Code	Purchaser
BA426	Jill
AP654	Adam
MF055	Fred
MT323	Charles

<tbody>
</tbody>

So the end goal is to find the purchaser (Table 2) from the code (Table 1 & 2) taken from the text field (Table 1).

Thanks to everyone who checks this out!

____________
Jawnathin

 

[Momentman]

Something like this should work for the first part, Excel 2010

	A	B	C
1	01/01/2013	Bananas BA426 Them more text	BA426
2	05/01/2013	Apples AP654 More additional text	AP654
3	06/03/2013	More Fruit MF055 Even more text	MF055
4	07/05/2013	More Text Before Numbers MT323 and this one is going to be really long to prove a point.	MT323

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula C1 {=MID(B1,MATCH(TRUE,ISNUMBER(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-2,5)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Finally you have something like this, in my data, the lookup table is in A7:B11, change as requiredExcel 2010

	A	B	C
1	01/01/2013	Bananas BA426 Them more text	Jill
2	05/01/2013	Apples AP654 More additional text	Adam
3	06/03/2013	More Fruit MF055 Even more text	Fred
4	07/05/2013	More Text Before Numbers MT323 and this one is going to be really long to prove a point.	Charles
5
6
7	Code	Purchaser
8	BA426	Jill
9	AP654	Adam
10	MF055	Fred
11	MT323	Charles

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula C1 {=VLOOKUP(MID(B1,MATCH(TRUE,ISNUMBER(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-2,5),A7:B11,2,0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[jawnathin]

Yep, that does it! Thanks a million, Momentman!

____________
Jawnathin