Multilines for Default text of a Macro Text Box

[Tibo]

Hi Guys,

I'm building a macro that would allow cleansing of some bad string such as #N/A #NULL! #NAME? (typical excel error values). The macro would replace these by null but I'd also like to give the option to edit the list of values that would be cleaned.

I was thinking about uisng the default text in the Text Box tool and allow for multiline but there's only one line under default text and I'm not sure if I need to use a specific format to input several values.

Any idea?

Thanks

Tibo

[ThizViz]

The way I've handle something like this in the past is to use the text input tool to hold all my variations on text I want to find (and a column for the replacement value, which is NULL in your case), and then connect that to a find/replace tool. This allows me to find bad text anywhere in a field, and replaces the entire field with a new value. I believe others have used dynamic replace for things like this, but I haven't had a chance to play with that tool yet. You may want to try it out.

[Tibo]

Thanks Thizwiz. It is indeed a workaround but I'd like that list to be incorporated in a macro (that does a number of other things) and editable by the user. I think I might just use the text input tool with some default values like you suggested and then add the Text Box to append additional values to that list. I'll keep the post open if someone has a better option.

[SophiaF · Accepted solution]

Let me know if I'm totally off here - I think a combination of both suggestions would work here. 

1. Allow user to input a list of 'bad strings', leaving #N/A,#NULL!,#NAME? as the default (note the commas here)

2. Parse out the list into a single column, then use a Find and Replace to look for those values and replace with nulls

Instead of using multiline, I would just have users add on to the default if they want additional values. That way they can remove them if for some reason they want to keep them. I attached a macro example along with a sample workflow containing the macro.

[Tibo]

That will do. Thanks!