Forum Discussion
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...
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_
=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_