| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding text
Hi
I have a colum that has a ID numbers as follows A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 In another column in receive ID numbers say EE123 i need a formula that will search column A and return the first ID where EE123 exists via =Mid(1,find(" ") as ID are between 4-6 characters long. So for EE123 the result would be E8954. All help is greatly appreciated. -- _______________________ Naz, London |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding text
Assuming the data as posted is all in col A, in A1 down
In B1: =IF(ISNUMBER(FIND("EE123",A1)),LEFT(A1,SEARCH(" ",A1)-1),"") Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Naz" wrote: I have a colum that has a ID numbers as follows A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 In another column in receive ID numbers say EE123 i need a formula that will search column A and return the first ID where EE123 exists via =Mid(1,find(" ") as ID are between 4-6 characters long. So for EE123 the result would be E8954. All help is greatly appreciated. -- _______________________ Naz, London |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding text
Hi Max
Thanks for your response..... ....this is close but not not what i'm looking for...let me try explaining better A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 [Sheet 1] These ID exist on sheet 1, but the ID i get are in a colum on sheet 2 so i will get A B C D E 1 E32185 2 EE123 3 E4564 [Sheet 2] The result that i need would be in col 2, sheet 2 as below A B C D E 1 E32185 E1234 [so finds E32185 in col1 sheet 1 2 EE123 E8954 and returns the first ID in the cell] 3 E4564 E3215 [Sheet 2] Hope that makes it clearer....thanks for all help. -- _______________________ Naz, London "Max" wrote: Assuming the data as posted is all in col A, in A1 down In B1: =IF(ISNUMBER(FIND("EE123",A1)),LEFT(A1,SEARCH(" ",A1)-1),"") Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Naz" wrote: I have a colum that has a ID numbers as follows A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 In another column in receive ID numbers say EE123 i need a formula that will search column A and return the first ID where EE123 exists via =Mid(1,find(" ") as ID are between 4-6 characters long. So for EE123 the result would be E8954. All help is greatly appreciated. -- _______________________ Naz, London |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding text
Try this:
In Sheet2: B1: =INDEX(LEFT(Sheet1!$A$1:$A$3,FIND(" ",Sheet1!$A$1:$A$3)-1),MATCH("*"&A1&"*",Sheet1!$A$1:$A$3,0)) copy down "Naz" wrote: Hi Max Thanks for your response..... ...this is close but not not what i'm looking for...let me try explaining better A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 [Sheet 1] These ID exist on sheet 1, but the ID i get are in a colum on sheet 2 so i will get A B C D E 1 E32185 2 EE123 3 E4564 [Sheet 2] The result that i need would be in col 2, sheet 2 as below A B C D E 1 E32185 E1234 [so finds E32185 in col1 sheet 1 2 EE123 E8954 and returns the first ID in the cell] 3 E4564 E3215 [Sheet 2] Hope that makes it clearer....thanks for all help. -- _______________________ Naz, London "Max" wrote: Assuming the data as posted is all in col A, in A1 down In B1: =IF(ISNUMBER(FIND("EE123",A1)),LEFT(A1,SEARCH(" ",A1)-1),"") Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Naz" wrote: I have a colum that has a ID numbers as follows A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 In another column in receive ID numbers say EE123 i need a formula that will search column A and return the first ID where EE123 exists via =Mid(1,find(" ") as ID are between 4-6 characters long. So for EE123 the result would be E8954. All help is greatly appreciated. -- _______________________ Naz, London |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding text
Thats the cookie!!!
Many thanks works perfectly. -- _______________________ Naz, London "Teethless mama" wrote: Try this: In Sheet2: B1: =INDEX(LEFT(Sheet1!$A$1:$A$3,FIND(" ",Sheet1!$A$1:$A$3)-1),MATCH("*"&A1&"*",Sheet1!$A$1:$A$3,0)) copy down "Naz" wrote: Hi Max Thanks for your response..... ...this is close but not not what i'm looking for...let me try explaining better A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 [Sheet 1] These ID exist on sheet 1, but the ID i get are in a colum on sheet 2 so i will get A B C D E 1 E32185 2 EE123 3 E4564 [Sheet 2] The result that i need would be in col 2, sheet 2 as below A B C D E 1 E32185 E1234 [so finds E32185 in col1 sheet 1 2 EE123 E8954 and returns the first ID in the cell] 3 E4564 E3215 [Sheet 2] Hope that makes it clearer....thanks for all help. -- _______________________ Naz, London "Max" wrote: Assuming the data as posted is all in col A, in A1 down In B1: =IF(ISNUMBER(FIND("EE123",A1)),LEFT(A1,SEARCH(" ",A1)-1),"") Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Naz" wrote: I have a colum that has a ID numbers as follows A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 In another column in receive ID numbers say EE123 i need a formula that will search column A and return the first ID where EE123 exists via =Mid(1,find(" ") as ID are between 4-6 characters long. So for EE123 the result would be E8954. All help is greatly appreciated. -- _______________________ Naz, London |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding text
You're Welcome!
"Naz" wrote: Thats the cookie!!! Many thanks works perfectly. -- _______________________ Naz, London "Teethless mama" wrote: Try this: In Sheet2: B1: =INDEX(LEFT(Sheet1!$A$1:$A$3,FIND(" ",Sheet1!$A$1:$A$3)-1),MATCH("*"&A1&"*",Sheet1!$A$1:$A$3,0)) copy down "Naz" wrote: Hi Max Thanks for your response..... ...this is close but not not what i'm looking for...let me try explaining better A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 [Sheet 1] These ID exist on sheet 1, but the ID i get are in a colum on sheet 2 so i will get A B C D E 1 E32185 2 EE123 3 E4564 [Sheet 2] The result that i need would be in col 2, sheet 2 as below A B C D E 1 E32185 E1234 [so finds E32185 in col1 sheet 1 2 EE123 E8954 and returns the first ID in the cell] 3 E4564 E3215 [Sheet 2] Hope that makes it clearer....thanks for all help. -- _______________________ Naz, London "Max" wrote: Assuming the data as posted is all in col A, in A1 down In B1: =IF(ISNUMBER(FIND("EE123",A1)),LEFT(A1,SEARCH(" ",A1)-1),"") Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Naz" wrote: I have a colum that has a ID numbers as follows A B C D E 1 E1234 E32185 E321 2 E8954 EC123 EE123 3 E3215 EE124 E4564 In another column in receive ID numbers say EE123 i need a formula that will search column A and return the first ID where EE123 exists via =Mid(1,find(" ") as ID are between 4-6 characters long. So for EE123 the result would be E8954. All help is greatly appreciated. -- _______________________ Naz, London |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding a text box | Excel Worksheet Functions | |||
| Finding text in a cell and returning a value based on that text | Excel Discussion (Misc queries) | |||
| Finding Buried Text | Excel Discussion (Misc queries) | |||
| Finding a particular text | Excel Discussion (Misc queries) | |||
| Finding Specific Text in a Text String | Excel Worksheet Functions | |||
Hybrid Mode
