This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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:
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.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and restart. Order shall return. Please Subscribe to my youTube channel.
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!