Forum Discussion

Andrew Hall's avatar
Andrew Hall
Copper Contributor
Oct 07, 2017

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

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?

excel
Excel Online
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 09, 2017
    A formula like this should do it:
    =LEFT(B3,FIND("_ORD",B3))&LEFT(SUBSTITUTE(B3,LEFT(B3,FIND("_ORD",B3)),""),FIND("_",SUBSTITUTE(B3,LEFT(B3,FIND("_ORD",B3)),"")))

    The formula works like this:
    Lets take 19_Decline_Left Barrel_Barrel_ORD1_LEFT_18_21 as the example.
    LEFT(B3,FIND("_ORD",B3)) finds "_ORD" and returns the text up to and including the underscore just before "ORD":
    19_Decline_Left Barrel_Barrel_
    The nthe substitute function takes the entire content and replaces "19_Decline_Left Barrel_Barrel_" with empty text. This gives:
    ORD1_LEFT_18_21
    The formula then finds the first underscore and returns everything to the left of it:
    ORD1_
    Added together:
    19_Decline_Left Barrel_Barrel_ORD1_

Resources