Pulling Data out of the middle of a cell to another Cell

[bclor3591]

Good Afternoon,

Here is the information that I'm working with. This is a portion of an EDI ERROR email with the trading partner information (it always falls right after the FIRST /)

I have highlighted the portion that I want to parse to another column/cell. the position varios depending on the Trading Partner name but the TP ID always follows the FIRST / in every email.

OUR COMPANY NAME[11:11223344410]/ABC COMPANY INC[01:123456789]/810ANY</SPAN></SPAN>
OUR COMPANY NAME[11:11223344410]/ZZZZZ COMPANY INC[11:123456789]/852ANY</SPAN></SPAN>
OUR COMPANY NAME[11:11223344410]/THIS IS ANOTHER COMPANY INC[21:123456789]/850ANY</SPAN></SPAN>
OUR COMPANY NAME[11:11223344410]/A CO[01:123456789]/810ANY</SPAN></SPAN>


What I'm planning on doing is taking this new cell information and do a VLOOKUP to another spreadsheet to get their email address and then send them an email telling them that their EDI document failed due to syntax errors. I tried using find with mid but not working correctly. Any help would be greatly appreciated.
Bob

 

[JoeMo]

Excel Workbook
	A	B
1	OUR COMPANY NAME[11:11223344410]/ABC COMPANY INC[01:123456789]/810ANY	01:123456789
2	OUR COMPANY NAME[11:11223344410]/ZZZZZ COMPANY INC[11:123456789]/852ANY	11:123456789
3	OUR COMPANY NAME[11:11223344410]/THIS IS ANOTHER COMPANY INC[21:123456789]/850ANY	21:123456789
4	OUR COMPANY NAME[11:11223344410]/A CO[01:123456789]/810ANY	01:123456789
Sheet13

 

[bclor3591]

Thank you Joe,

That worked perfectly! I was trying to use trim and mid but not knowing how to interact with the two functions made it difficult. You made it look easy!!
Bob

 

[JoeMo]

Thank you Joe,

That worked perfectly! I was trying to use trim and mid but not knowing how to interact with the two functions made it difficult. You made it look easy!!
Bob

You are welcome - thanks for the reply.