Thread: Removing multiple invalid characters for dependent data validation list

Can anyone please tell me how to remove multiple numbers or other unacceptable characters for a cell range name?

Essentially I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning.

The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:

00 - Preconstruction
01 - General Conditions

I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.


Second thought:

If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work?

Ultimately I want to use these 2 drop downs for reference functions on a second worksheet


Please let me know if this doesn't make sense. I know it's a bit convoluted.

Thanks!

There are a couple of ways to do this. If your layout is consistent and you want the text after the "-" without spaces, you can use this DV formula:

=INDIRECT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2))," ",""))

If you want to use more controlled list names, you can actually create a lookup table in your sheet somewhere. The first column could be called List and could be used as the DV source in the first drop box. The second column is the name of the matching list, and can appropriately be called Lists.

Code:

	  	A				B
1	00 - Preconstruction		Preconstruction
2	01 - General Conditions		GeneralConditions
3	02 - Fair Parts			FairParts

Then you use this as your Data Validation formula:
=INDIRECT(LOOKUP(A2, List, Lists))

With this second method, you are no longer constrained by the text, your LISTS can have any list name values you want.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)