Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding numbers or numbers and dashes in text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding numbers or numbers and dashes in text
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) On 29 Mar, 10:02, wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding numbers or numbers and dashes in text
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding numbers or numbers and dashes in text
Can anybody help? Thanks
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding numbers or numbers and dashes in text
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 On 29 Mar, 11:01, wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding numbers or numbers and dashes in text
Thanks Lori, that works for the first column, but i just get zeros if
i try and find a second ref. Cheers. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding numbers or numbers and dashes in text
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 ..... On 2 Apr, 09:07, wrote: Thanks Lori, that works for the first column, but i just get zeros if i try and find a second ref. Cheers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to change numbers with dashes to numbers with no dashes | Excel Discussion (Misc queries) | |||
Help with finding numbers within text and summing | Excel Worksheet Functions | |||
Convert phone numbers with dashes in them to just numbers | Excel Discussion (Misc queries) | |||
Convert phone numbers with dashes in them to just numbers | Excel Discussion (Misc queries) | |||
Finding the Value (of text and numbers) between N/A's | Excel Programming |