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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Created[/TD]
[TD]Comment[/TD]
[TD]Desired Extraction[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]Bananas BA426 Them more text[/TD]
[TD]BA426[/TD]
[/TR]
[TR]
[TD]5/1/2013[/TD]
[TD]Apples AP654 More additional text[/TD]
[TD]AP654[/TD]
[/TR]
[TR]
[TD]6/3/2013[/TD]
[TD]More Fruit MF055 Even more text[/TD]
[TD]MF055[/TD]
[/TR]
[TR]
[TD]7/5/2013[/TD]
[TD]More Text Before Numbers MT323 and this one is going to be really long to prove a point. [/TD]
[TD]MT323[/TD]
[/TR]
</tbody>[/TABLE]

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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Purchaser[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BA426[/TD]
[TD]Jill[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP654[/TD]
[TD]Adam[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MF055[/TD]
[TD]Fred[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MT323[/TD]
[TD]Charles[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

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
BA426
AP654
MF055
MT323

<tbody>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #FFFFFF"]01/01/2013
[/TD]
[TD="bgcolor: #FFFFFF"]Bananas BA426 Them more text
[/TD]

[TD="align: center"]2
[/TD]
[TD="bgcolor: #FFFFFF"]05/01/2013
[/TD]
[TD="bgcolor: #FFFFFF"]Apples AP654 More additional text
[/TD]

[TD="align: center"]3
[/TD]
[TD="bgcolor: #FFFFFF"]06/03/2013
[/TD]
[TD="bgcolor: #FFFFFF"]More Fruit MF055 Even more text
[/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: #FFFFFF"]07/05/2013
[/TD]
[TD="bgcolor: #FFFFFF"]More Text Before Numbers MT323 and this one is going to be really long to prove a point.
[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C1
[/TH]
[TD="align: left"]{=MID(B1,MATCH(TRUE,ISNUMBER(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-2,5)}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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

	A	B	C
Jill
Adam
Fred
Charles

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]01/01/2013[/TD]
[TD="bgcolor: #FFFFFF"]Bananas BA426 Them more text[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]05/01/2013[/TD]
[TD="bgcolor: #FFFFFF"]Apples AP654 More additional text[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]06/03/2013[/TD]
[TD="bgcolor: #FFFFFF"]More Fruit MF055 Even more text[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]07/05/2013[/TD]
[TD="bgcolor: #FFFFFF"]More Text Before Numbers MT323 and this one is going to be really long to prove a point.[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]Code[/TD]
[TD="bgcolor: #FFFFFF"]Purchaser[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]BA426[/TD]
[TD="bgcolor: #FFFFFF"]Jill[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]AP654[/TD]
[TD="bgcolor: #FFFFFF"]Adam[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]MF055[/TD]
[TD="bgcolor: #FFFFFF"]Fred[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]MT323[/TD]
[TD="bgcolor: #FFFFFF"]Charles[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]{=VLOOKUP(MID(B1,MATCH(TRUE,ISNUMBER(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-2,5),A7:B11,2,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[jawnathin]

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

____________
Jawnathin