Hi,

Can anyone help me to find out this formula?

Column A1
The company ABCD1237 ID:xxxxxxxxx3 ABCD company
Company abcd1237 ID:xxxxxxxx4 Thankyoucompany

The result I'd like to get is only the ID:xxxxxxxx3. I have a formula but
it only take the first part ID:xxxxxxxxx3 ABCD company. I'd like to get rid
of ABCD company portion as well. Here is my formula.
=MID(O27,FIND(" ",O27)+9,LEN(O27))

Any suggestions? Thank you.

Hi,

=TRIM(LEFT(MID(O27,FIND(" ",O27)+9,LEN(O27)),FIND(" ",MID(O27,FIND("
",O27)+9,LEN(O27)))-1))

if this helps please click yes, thanks

"momotaro" wrote:

Hi,

Can anyone help me to find out this formula?

Column A1
The company ABCD1237 ID:xxxxxxxxx3 ABCD company
Company abcd1237 ID:xxxxxxxx4 Thankyoucompany

The result I'd like to get is only the ID:xxxxxxxx3. I have a formula but
it only take the first part ID:xxxxxxxxx3 ABCD company. I'd like to get rid
of ABCD company portion as well. Here is my formula.
=MID(O27,FIND(" ",O27)+9,LEN(O27))

Any suggestions? Thank you.

Hi,,

the bit to extract is always in the same place in the string try this

=MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256),FIND("^",SUBSTITUTE(A1,"
","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1,"
","^",3),1,256),FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2)

Mike

"momotaro" wrote:

Hi,

Can anyone help me to find out this formula?

Column A1
The company ABCD1237 ID:xxxxxxxxx3 ABCD company
Company abcd1237 ID:xxxxxxxx4 Thankyoucompany

The result I'd like to get is only the ID:xxxxxxxx3. I have a formula but
it only take the first part ID:xxxxxxxxx3 ABCD company. I'd like to get rid
of ABCD company portion as well. Here is my formula.
=MID(O27,FIND(" ",O27)+9,LEN(O27))

Any suggestions? Thank you.

=LEFT(RIGHT(A1,LEN(A1)-FIND("ID",A1,1)+1),FIND("
",RIGHT(A1,LEN(A1)-FIND("ID",A1,1)),1))

--
Gary''s Student - gsnu200902

Thanks. I used your formula but got "#VALUE!". Suggessions please. Thanks
again.



"Eduardo" wrote:

Hi,

=TRIM(LEFT(MID(O27,FIND(" ",O27)+9,LEN(O27)),FIND(" ",MID(O27,FIND("
",O27)+9,LEN(O27)))-1))

if this helps please click yes, thanks

"momotaro" wrote:

Hi,

Can anyone help me to find out this formula?

Column A1
The company ABCD1237 ID:xxxxxxxxx3 ABCD company
Company abcd1237 ID:xxxxxxxx4 Thankyoucompany

The result I'd like to get is only the ID:xxxxxxxx3. I have a formula but
it only take the first part ID:xxxxxxxxx3 ABCD company. I'd like to get rid
of ABCD company portion as well. Here is my formula.
=MID(O27,FIND(" ",O27)+9,LEN(O27))

Any suggestions? Thank you.

Thanks. I tried this one but did not work. I got "#value". Any
suggestions? Thank you again.

"Gary''s Student" wrote:

=LEFT(RIGHT(A1,LEN(A1)-FIND("ID",A1,1)+1),FIND("
",RIGHT(A1,LEN(A1)-FIND("ID",A1,1)),1))

--
Gary''s Student - gsnu200902

Hi,
It is working for me I copy your data and it works, check if you don't have
and extra space in your text

"Eduardo" wrote:

Hi,

=TRIM(LEFT(MID(O27,FIND(" ",O27)+9,LEN(O27)),FIND(" ",MID(O27,FIND("
",O27)+9,LEN(O27)))-1))

if this helps please click yes, thanks

"momotaro" wrote:

Hi,

Can anyone help me to find out this formula?

Column A1
The company ABCD1237 ID:xxxxxxxxx3 ABCD company
Company abcd1237 ID:xxxxxxxx4 Thankyoucompany

The result I'd like to get is only the ID:xxxxxxxx3. I have a formula but
it only take the first part ID:xxxxxxxxx3 ABCD company. I'd like to get rid
of ABCD company portion as well. Here is my formula.
=MID(O27,FIND(" ",O27)+9,LEN(O27))

Any suggestions? Thank you.

Thank you all. I found my error. It works now.

"momotaro" wrote:

Hi,

Can anyone help me to find out this formula?

Column A1
The company ABCD1237 ID:xxxxxxxxx3 ABCD company
Company abcd1237 ID:xxxxxxxx4 Thankyoucompany

The result I'd like to get is only the ID:xxxxxxxx3. I have a formula but
it only take the first part ID:xxxxxxxxx3 ABCD company. I'd like to get rid
of ABCD company portion as well. Here is my formula.
=MID(O27,FIND(" ",O27)+9,LEN(O27))

Any suggestions? Thank you.