Can someone please tell me if it is possible to write a formula that would
extract all the text that exists between the vertical bar symbol "|".

Example in the following Cell A1 contains:
W213FR|BADC12GW34-14|SPACER, DEG

I want to extract only "BADC12GW34-14" and put it in another cell, say B1.
Thanks!

This seems to work:

=MID(A1,FIND("|",A1)+1,FIND("|",MID(A1,FIND("|",A1 )+1,99))-1)

Hopefully someone else will come up with a shorter version.

Regards,
Fred.

"Tony S." wrote in message
...
Can someone please tell me if it is possible to write a formula that would
extract all the text that exists between the vertical bar symbol "|".

Example in the following Cell A1 contains:
W213FR|BADC12GW34-14|SPACER, DEG

I want to extract only "BADC12GW34-14" and put it in another cell, say B1.
Thanks!

Enter the following in cell B1:


=MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)



"Tony S." wrote:

Can someone please tell me if it is possible to write a formula that would
extract all the text that exists between the vertical bar symbol "|".

Example in the following Cell A1 contains:
W213FR|BADC12GW34-14|SPACER, DEG

I want to extract only "BADC12GW34-14" and put it in another cell, say B1.
Thanks!

If the text to test is in A1, you can use

=MID(A1,(FIND("|",A1,1)+1),(FIND("|",A1,(FIND("|", A1,1)+1)+1))-(FIND("|",A1,1)+1))

This will return a #VALUE error if there are not two | characters in
A1. You can test for that condition with the expanded formula

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"|","")) <2,NA(),
MID(A1,(FIND("|",A1,1)+1),(FIND("|",A1,(FIND("|",A 1,1)+1)+1))-(FIND("|",A1,1)+1)))



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 6 Nov 2008 14:54:04 -0800, Tony S.
wrote:

Can someone please tell me if it is possible to write a formula that would
extract all the text that exists between the vertical bar symbol "|".

Example in the following Cell A1 contains:
W213FR|BADC12GW34-14|SPACER, DEG

I want to extract only "BADC12GW34-14" and put it in another cell, say B1.
Thanks!

Fred & FiluDlidu,

Your answers were exactly what I needed. Thank you!
Chip, a special thanks to you for going the extra mile with your error
trapping example. Great stuff!

Tony

"Chip Pearson" wrote:

If the text to test is in A1, you can use

=MID(A1,(FIND("|",A1,1)+1),(FIND("|",A1,(FIND("|", A1,1)+1)+1))-(FIND("|",A1,1)+1))

This will return a #VALUE error if there are not two | characters in
A1. You can test for that condition with the expanded formula

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"|","")) <2,NA(),
MID(A1,(FIND("|",A1,1)+1),(FIND("|",A1,(FIND("|",A 1,1)+1)+1))-(FIND("|",A1,1)+1)))



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 6 Nov 2008 14:54:04 -0800, Tony S.
wrote:

Can someone please tell me if it is possible to write a formula that would
extract all the text that exists between the vertical bar symbol "|".

Example in the following Cell A1 contains:
W213FR|BADC12GW34-14|SPACER, DEG

I want to extract only "BADC12GW34-14" and put it in another cell, say B1.
Thanks!