Finding text

[Naz]

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.

 

[Max]

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

 

[Naz]

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.

 

[Teethless mama]

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

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

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

 

[Naz]

Thats the cookie!!!
Many thanks works perfectly.

--

_______________________
Naz,
London

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

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

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

---
:
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

 

[Teethless mama]

You're Welcome!

Thats the cookie!!!
Many thanks works perfectly.

--

_______________________
Naz,
London

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

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


:

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

---
:
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