Data Cleaning - What is the best method?

[shpruitt]

I'm new and struggling. I am trying to data cleanse columns of data that say 'USA-Senior Associate' and 'USA-Competency' and I would like to remove the 'USA-' from both sets. How do I do this?

Thanks

[BenMoss]

A few options here but I'd go with the simplest in my eyes which would be to use the text to columns with a - delimiter.

Ben

[MarqueeCrew · Accepted solution]

@BenMoss,

Windex!  Get something to clean those glasses.

Replace([_CurrentField], "USA-", '')

What could be easier than this formula (which by the way will work nicely in a multi-field formula tool.

Cheers,

Mark

[Bob_Blackey]

Just to throw another option in there (SO many ways to do things in Alteryx), and keeping in the vein of the using the Multi_Field Formula Tool to clean several fields;

Substring([_CurrrentField_],FindString([_CurrentField_],"-")+1)

Findstring finds the 0 based position of the -

Substring starts at the character after the - and takes the rest of the string

This would work if you have other prefixes besides USA

I'm using _CurrentField_ here as that's the format of the Multi_field Formula tool - in a standard formula tool replace it with the field name.

Cheers,

Bob

[shpruitt]

So I have inserted the multi-field formula after my import of data.

I selected both columns (V-strings) that I would like to start with (as you guessed there are many that need the 'USA-' gone).
The Copy Output Fields Add box is already checked so I left it alone (it says 'New_ as a prefix).

I tried your formula and I tried Replace([_CurrentField_], "USA-", "") and neither of those seem to be doing anything. I did it in the single formula option and did get it to work - but I would love to learn how to use this multi-formula instead of cheating and doing 3-4 formulas right after each other.

[BenMoss]

Be sure to either scroll right in your results window, or deselect the option 'Copy output fields Add'. If you have this option checked it will create as many knew fields as those selected which will be added to the right side of your table, commonly out of view.

Ben

[The_Data_Loop]

You could also simply pad the first 4 characters out.