Trim unknown character return right not left

[LINDAMG]

Hi - I am trying to extract the back half of a text string. example: Linda Marie | LINDAM or Julie Smith Jones | JSJS. I want to bring back the LMGOVIN and the JSJS. Basically everything after the "|" (there are 2 spaces after the "|" if that matters.

I can get the item in front of the "|" with TRIM(LEFT(A1, FIND("|",A1)-2)),"") and tried variations to get the other half, but to no avail.

Anyway to get this to work?

 

[Roderick_E]

Assuming it will always have only one | divider

=TRIM(MID(A1,FIND("|",A1)+1,LEN(A1)))

 

[jtakw]

Hi,

A couple more options:

B1 formula to extract everything After the pipe "|"

B4 formula copied across to extract everything Before the pipe in B4, and everything After the pipe in C4

Book1
	A	B	C
1	Linda Marie | LINDAM	LINDAM
2	Julie Smith Jones | JSJS	JSJS
3
4	Linda Marie | LINDAM	Linda Marie	LINDAM
5	Julie Smith Jones | JSJS	Julie Smith Jones	JSJS
Sheet277

Cell Formulas
Range	Formula
B1	=TRIM(REPLACE(A1,1,FIND("|",A1),""))
B4	=TRIM(MID(SUBSTITUTE($A4,"|",REPT(" ",100)),COLUMNS($B4:B4)*100-99,100))

 

[Joyner]

If you don't have to use a formula, you could use Flash Fill or Text to Column to extract those - two quick options

 

[LINDAMG]

Did exactly what I needed! Thank you so much.