Sharepoint Input Tool Data Cleanup

fitzviz

I have pulled my data in using the Sharepoint input tool, however a bunch of my columns have the internal code pulled through from pick lists, for example "146;#United States" where all I want in my file is "United States" Is there a quick and systematic way to clean this up in Alteryx other than parse out to text to columns then using data cleansing? Essentially I just want to truncate everything before the #... Assuming this is a common question with Sharepoint data? Thanks!!

NicoleJohnson

You should be able to use Regular Expression formula in the Multi-Field tool to accomplish this...

1. Add Multi-Field Formula tool.

2. Select the columns that have the internal code pulled through from pick lists. (Uncheck the box to Copy Output fields to a new field if you just want the formula to fix them in their original fields).

3. Use this expression to remove the initial numbers and ;# from before the actual field values:

REGEX_Replace(tostring([_CurrentField_]), "\d*;#(.*)","$1")

For all fields that are checked, it will use this expression to keep only whatever is in the parentheses (marked field). Let me know if that works for you! RegEx is a beautiful thing...

NJ