SOLVED

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

New Contributor

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?

5 Replies

Andrew,

 

D3:F3

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

It worked.

Thanks for the quick reply. Appreciate it.
Best Response confirmed by Andrew Hall (New Contributor)
Solution

Detlef,

 

As variant (without intermediate cells)

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