Multilines for Default text of a Macro Text Box

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

Macros

Interface Tools

Accepted answers

SophiaF

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.

test_workflow.yxzp

All comments

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

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

test_workflow.yxzp

Tibo

That will do. Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703