String Parsing from Specific Point

[Afammy]

Hello all, 

I have this string "US CRE Head (Joe Shmoe)". I am trying to create a column that includes only everything before the "Head". What is the best method? Thanks.

[Bob_Blackey · Accepted solution]

You can use the Regex Tool (in the parse Toolset)

In the config:

1) Choose the field to Parse

2) type in the regular expression: (.*)Head.*

3) Set the output method to Parse

That will create a new field with whatever is before head.

I highly reccomend learning Regex in you're unfamilar - it is the secret to the universe.

[Afammy]

Worked like a charm, thanks Bob! I had a feeling regex was the answer but couldn't find a beginner resource yet. If you know of one feel free to share. Have a great day.

[Philip]

I agree with the RegEx solution. Just to show an alternative if you're not ready to dive into RegEx, you could use a Formula Tool with this:

TRIM(Left([Field], FindString([Field], "Head")))

Just replace [Field] with the name of the field containing the string. The TRIM removes any whitespace, the Left takes the characters to the left of the location FindString provides.

[MarqueeCrew]

@Bob_Blackey,

What is the meaning of life?  42.  Besides that, I agree RegEx!

I might caution @Afammy on the parse logic. 

(.*)Head.*

If "Head" exists multiple times in the field, this will parse out based upon the last (rightmost) occurence of the term "Head".

Other than that, we're in total agreement.

Cheers,

Mark

[Afammy]

Another great solution- thanks!

[Afammy]

Thanks for the head's up Mark. In my case I only have it once in each row so I'm all set. 

[Bob_Blackey]

No worries, Afammy.

There are quite a few online resources to learn regex - I did a quick Google search on "Regex Tutorials" and found one - www.regexone.com that seems good. if you want to really get into it the book, "Mastering Regular Expressions" by O'Reilly Media is great.

As @MarqueeCrew 's note suggests regex has a bit of nuance to it and you need to know your data (and how weird it can be) to know how detailed a regular expression to build. Also, the more you use it, the more you'll use it. If you only use it once a month or two I find you almost have to relearn it, but it you use it all the time it can quickly become your "go to tool" as it is incrediably powerful and can do things that are literally impossible with string formulas and other tools.

[KAFord]

Y'all are amazing!!! I have been struggling to remove information and this formula actually did the trick for me. I love this community and how everyone is so helpful! 

THANK YOU for saving me several hours trying to figure out the syntax! 

--Kim XX