Finding numbers or numbers and dashes in text

[jim.hatton]

Hi I have a bit of a problem. I have a list of IDs i would like to
extract from a column of cells in excel. The problem is they are not
fixed length or all numeric, etc. For example,

71420164
7115-0003
7115-0011
713005L
713005R

In addition there are 250 of these. To make matters more complicated,
the cells which contain the IDs are also random length and made up of
alphanumerics. For example,

"SCORPIO FEM COMPONENT SZ 5L REF: 71-3005L LOT:ET05V03 SCORPIO TIB.
TRAY SZ 3 REF: 7115-0003
LOT: 4EHMCA SCORPIO TIB INSERT 3/12MM REF: 72-13-0312 LOT: 16124501"

or

"Genesis 2 Non Porous Tibial Baseplate Sz 3 Ref: 71420164 Lot:
06BT95572 Genesis 2 Biconvex Patellar Sz 23mm Ref: 71420566 Lot:
05MM06587 Genesis 2 Cruciate Retaining Femoral Component Sz 4 Ref:
71420006
Lot: 06BM16921 Genesis 2 Cruciate Retaining Poly "

or

"13 FEM COMPONENT SCORPIO REF 71-3013L LOT 10045776MU 11 TIB BASE
REF 7115-0011 LOT Y1EMA000MAC 11/13 TIB INSERT REF 72-13-1110 LOT
10228737CM 11 PATELLA REF 73-0110 LOT G314FLAA713"

Is there a way i can get excel to lookup one of the IDs from a column,
search the alphanumeric cells and extract it to an adjacent column.
This is driving me crazy!

Thanks

 

[Lori]

Assume Sheet1!A1:A250 contains the alphanumeric strings and Sheet2!
A1:A250 contains the (non-blank) IDs. Try filling down from cell B1 in
sheet1:

=LOOKUP(9^9,SEARCH(Sheet2!C$1:C$250,A1),Sheet2!C$1:C$250)

 

[jim.hatton]

Thanks, this just returns a '0' where there is text or 'N/A' where it
si blank.

I want it to actually extract the ID from the random text. Can i
modify it to do this?

 

[jim.hatton]

Can anybody help? Thanks

 

[Lori]

Apologies, the C's should have been A's in the formula above. To allow
for blanks and ignore -s in formulas try instead filling down from B1:

=LOOKUP(9^9,SEARCH(SUBSTITUTE(Sheet2!A$1:A$250,"-",""),
SUBSTITUTE(A1,"-",""))/(Sheet2!A$1:A$250<>""),Sheet2!A$1:A$250)

and to look for a second ID, fill down in column C:

=LOOKUP(9^9,SEARCH(SUBSTITUTE(Sheet2!A$1:A$250,"",""),
SUBSTITUTE(SUBSTITUTE(A1,B1,""),"-",""))/(A$1:A$250<>""),Sheet2!A$1:A
$250)

Results:

B C
713005L 7115-0003
71420164 #N/A
7115-0011 #N/A

 

[jim.hatton]

Thanks Lori, that works for the first column, but i just get zeros if
i try and find a second ref.

Cheers.

 

[Lori]

Better to try a different approach if you're looking for more than one
match against a description.

Enter a "*" in the top of sheet 2 for the return value when there is
no match.
On sheet 1 try filling down this array formula (ctrl+shift+enter to
execute):

=INDEX(Sheet2!$A:$A,LARGE(ROW(Sheet2!$A$1:$A$251)*
ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$A$1:$A$251,"-",""),
SUBSTITUTE($A1,"-","")))*(Sheet2!$A$1:$A$251<>""),1))

Then repeat the formula in the next columns replacing the "1" in the
"large(...,1)" at the end of the formula by "2","3" etc.

This should give:

Sheet1
SCORPIO... 713005L 7115-0003 *
Genesis... 71420164 * *
13 FEM... 7115-0011 * *
.....

Sheet2
*
71420164
7115-0003
7115-0011
713005L
713005R
.....