How to Filter out text in a column of data

Question

Hello Alteryx community,

Silly question but I have a data sheet with names and unique locations to those names. Such is written as "name - location", and I just wanted to know if there was a way I could filter out just the names into a new column. The tricky thing I'm facing is that all the names vary in size.

mark007 · Answer

Like so many things in Alteryx, so many ways to achieve the same goal!  I'd personally go with a regular expression using regex.  The expression you need is (.*) - (.*) [although you could use others].  A nice introduction to the power of regex too!

Regex example.png

MarqueeCrew · Answer

@CMCesar,

Let's talk through your challenge!  You have data where a NAME is followed by a dash (-) which is followed by  a location.  You want to find the NAME only.  When we dig a little deeper, we will see that the name is followed by a space then the dash.  Maybe, it is followed by the dash directly?  You want just the Name.  Possibly, you may even encounter a record that has NO dash.  Maybe it is a name only or a location only.  The data challenges may exist that put any solution to the test.

There's a function that returns the location of a target string in your source data.  That function is:  findstring

Findstring([Transaction Description], "-")

This will return a zero-based location of the dash character.  For "MarqueeCrew - Michigan" it will return the value of  12.

We can now introduce you to the LEFT Function

left([Transaction Description],FindString([Transaction Description], "-"))

This will provide us with everything in the field up to the length provided in your string.  In this case, it is everything up to the dash, but because the findstring is 0-based it goes to the immediate left value of the dash.

We then have the NAME, but with the likelihood of a trailing space.  So now we can "trim" it of all spaces with:

TRIM(left([Transaction Description],FindString([Transaction Description], "-")))

Now how to tell if things worked?  If the output = input, then we did something wrong.  If you put a filter in before the formula tool, then you could bring in data where a dash is present.  Then you could union the data back together.  If you don't care, you have no worries.

Cheers,

Mark

stvnhdmpa · Answer

Here are two ways I thought of quickly.  One text to columns and the other using formulas.  You would need to do a little more cleansing.

CMCesar - Examples.yxmd