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

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

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

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

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

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