Extract Text left of a character (that is after a certain portion of text)

[Andrew Hall]

Hi, I want to extract the first portion of text with special conditions. I want everything left of the underscore that is after the characters ORD.

Here is an example:

• 19_Decline_Left Barrel_Barrel_ORD1_LEFT_18_21 Should return 19_Decline_Left Barrel_Barrel_ORD1_
• 56_Decline_Connections_ORD18_0 Should return 56_Decline_Connections_ORD18_
• 89_Raise_Raiseline_ORD5_0 Should return 89_Raise_Raiseline_ORD5_
• 120_Cross Cut_Travelling Way_ORD8LVL2_LEFT_0 Should return 120_Cross Cut_Travelling Way_ORD8LVL2_

Thank you. Any advice?

[#2]

Andrew,

D3:F3

=SEARCH("ORD",B3)
=SEARCH("_",MID(B3,D3,9^9))
=LEFT(B3,D3+E3-1)

[#3]

Hi Detlef,

It worked.

Thanks for the quick reply. Appreciate it.

[#4 · Accepted solution]

Detlef,

As variant (without intermediate cells)

=IFERROR(LEFT(B3,SEARCH("_",B3,SEARCH("ORD",B3))),"")

[#5]

Definitely better, Sergei.

[#6]

Thanks Sergei.