Modifying text

Question

Hi everyone,

I have two issues I'm dealing with:

1. I need to convert text; e.g., "MERGERS & ACQUISITIONS" to "M&A". I'm sure this isn't a difficult task to accomplish, but I cannot find the solution.

2. Even after using the data cleansing tool I still have leading spaces that I have to remove. I'm unsure of what I'm doing wrong.

I've attached my workflow and the input file, in case these are useful.

Thanks in advance.

Specialties workflow.yxmd

Current Corp Headcount.xlsx

flying008 · Accepted Answer

Hi,@lynnekilgore

Maybe you need Trim([Specialties]) after split the Column.

jbichachi003 · Accepted Answer

Hi @lynnekilgore,

Let's address your second issue first

The reason why you're seeing those leading spaces is because you need to flip the order of your tools. Try putting the Text to Columns tool before the Data Cleanse.

Some of your records contain a space immediately after the delimiter you've specified ("/"). Therefore, when Alteryx splits on that delimiter, the newly created rows (since you have the Text to Column configured to Split to rows) will contain that leading space. If you use the Data Cleanse tool after using the Text to Column tool, you'll remove that leading space. Note, you could also use a Formula tool with to remove any leading/trailing whitespace instead of the Data Cleanse tool. Trim([Specialties])

Now let's address your first issue

You can replace "MERGERS & ACQUISITIONS" with "M&A" in one of two ways. You can either use a conditional formula to see if a cell equals or contains "MERGERS & ACQUISITIONS" and replace it with "M&A" if it does (or leave it alone if it doesn't). This method will work if the cell only says "MERGERS & ACQUISITIONS". If it says "MERGERS & ACQUISITIONS" but also contains some other text, you run the risk of losing that other text as well.

You can alternatively use the Find Replace tool to replace the instances of "MERGERS & ACQUISITIONS" to "M&A" without possibly replacing anything else.

I've included both solutions (as well as a fix to your second issue) to the attached workflow.

Hope this helps!