How do I extract the left most word in a Column?

Jeffrey_PMO

I'm looking for a formula to extract the left-most word form a column that will have different character lengths.

Looking at the example below:

• Sometimes there is a / in front of that left-most word
• Sometimes its just a single word
• Sometimes there is a < in front of the left-most word

Thank you!

Paul Newcome

Try something like this...

=IF(FIND("<", [Parent Trailing]@row)> 0, LEFT([Parent Trailing]@row, FIND("<", [Parent Trailing]@row) - 1), IF(FIND("/", [PArent Trailing]@row)> 0, LEFT([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) - 1), [Parent Trailing]@row))

Paul Newcome

Here is a little tweak:

=IFERROR(MID([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2, FIND("/", [Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2) - (FIND("/", [Parent Trailing]@row) + 2)), MID([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2, LEN([Parent Trailing]@row) - (FIND("/", [Parent Trailing]@row))))

Paul Newcome

Try something like this...

=IF(FIND("<", [Parent Trailing]@row)> 0, LEFT([Parent Trailing]@row, FIND("<", [Parent Trailing]@row) - 1), IF(FIND("/", [PArent Trailing]@row)> 0, LEFT([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) - 1), [Parent Trailing]@row))

Jeffrey_PMO

As always, you saved me hours of frustration! Thank you Paul.

Paul Newcome

Happy to help. 👍️

Jeffrey_PMO

Hey @Paul Newcome - How would I tweak the formula to now pull the word after the / ?

I've tried tweaking the "<" symbols or the "-1" but havent had luck. In the screenshot below I'm either trying to pull the word "Plan", "Execute" or "Closing".

Thank you!

Paul Newcome

Give this one a go...

=IFERROR(MID([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2, FIND("/", [Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2) - (FIND("/", [Parent Trailing]@row) + 2)), MID([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2, LEN([Parent Trailing]@row) - (FIND("/", [Parent Trailing]@row) + 2)))

Jeffrey_PMO

That one works but only if there is / after the second "/".

Otherwise as shown below it will pull the the second word after the first "/" but leave out the last letter.

Paul Newcome

Here is a little tweak:

=IFERROR(MID([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2, FIND("/", [Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2) - (FIND("/", [Parent Trailing]@row) + 2)), MID([Parent Trailing]@row, FIND("/", [Parent Trailing]@row) + 2, LEN([Parent Trailing]@row) - (FIND("/", [Parent Trailing]@row))))

Jeffrey_PMO

Thank you Paul!

Paul Newcome

Happy to help. 👍️