String operations for many columns

[ulrich_schumann]

Dear all,

I received a data source with almost 100 columns. Unfortunately almost all cells contain in brackets the year when the data was last updated. Usually I would run a string operation to remove all unwanted entries the the row and would repeat where additional needed. But I don't want to run this string operation for almost 100 fields. Is there any chance to apply the string operation for all existing columns?

Thanks for your support!

[RodL]

Perfect case for the Multi-Field Formula tool.

You can apply the same formula you normally use for one column to multiple columns at the same time.

[ulrich_schumann]

Hi RodL,

I still have some problems as

- not all the fields have the year in brackets

- sometimes there is just a (0) instead of e.g. (2015)

so I cannot just apply a sting operation "right". I uploaded an short example to show the details.

I also tried to just switch the field from string to double which removes the brackets but the decimals as well.

So I cannot use the Multi-Field Formula, can I?

[ulrich_schumann · Accepted solution]

When I exclude all  'real' text fields within the Multi-Field Formula tool this string operation worked for me. Still quite a lot of effort to go through all fields, but at least it solved my problem.

Left([_CurrentField_], Length([_CurrentField_])-(Length([_CurrentField_])-FindString([_CurrentField_], "(")))

[RodL · Accepted solution]

After looking at your data, I'm thinking this expression should work with all Text fields selected...

Trim(REGEX_Replace([_CurrentField_], "\(\d*\)", ""))

With the consistency in the values in the parentheses, the RegEx is an easier way to go.

[ulrich_schumann]

Great - this works even better!