Help for a Newbie who is used to Excel - finding string and trimming

[bobby_d]

Hi there - I am relatively new to Alteryx and trying to do something that I know I can do easily within Excel but looking at the boards here it appears to be very difficult and I dont want to keep reverting back to my Excel comfort zone.

Not the exact problem, but part of it is that I have a column of relationships, containing

EMPLOYEE - FEMALE,

EMPLOYEE

i simply want to end up with EMPLOYEE in each column.

In excel I would use the FIND function to do something like find "-" which will return 10 (if I have counted correctly) and the LEFT function to return the left 10 characters.

Can we do this with a simple method in Alteryx?

[markcurry · Accepted solution]

You can do this in much the same way as in Excel, using the FindString function.  Or you could use the Text to Columns tool, see attached examples...

[Blake]

Hi @bobby_d 

There are a lot of ways to approach this problem and most of them will take one tool to solve. It'd be a nice to have a sample of your data to work with but check out what I mocked up below and in the attached workflow. 

You could use a conditional formula to overwrite the field to "Employee" anytime it contains the word "Employee"

You could also use RegEx to grab the first word in the field. 

There are surely other ways you could do this but I think either of these solutions will work. Let me know if this helps! 

[markcurry]

You can find all the String functions, but clicking on the 'fx' button within the Formula tool and scrolling down to the String category.   To remove the whitespace you can either use the Trim function, the Data Cleansing tool, or you can modify your Left function to subtract 1, from where it has found the position of the dash    Left([Data],[Find]-1)

 

[GeorgeD]

I just started using Alteryx as well. I was able to easily do this by adding the "Data Cleansing" tool in the Preparation tab. In the "Remove Unwanted Characters" section, select "All Whitespace" and "Punctuation". That should do the trick.

[bobby_d]

Thanks to you (and all that answered).  I was half way there - I just thought it had gone wrong when it returned -1 for the first line.  I didnt realise that was intentional.