This post demonstrates how to remove underscore from a defined name range in excel while the name range populates in a drop down.
If you have created a lot of dependent drop downs in excel based on the name range, you are probably aware that when you define the name of the range with spaces in it, excel converts those spaces into underscore.
In situations where we are required to populate drop downs that show those name ranges appears with underscore embedded between them which kind of looks odd.
Here I am intending to select the continent in the drop down in cell A1 and any one of the country in the drop down in cell A2, my name range has been defined as South_America which has the range F2 to F4
I would like to show the value as “South America” and not as “South_America” which is the result of conversion of blank space in our name range to underscore.
To make excel understand that South America is same as South_America named range, we would use a simple SUBSTITUTE formula in excel which will substitute underscore with blanks.
Use the formula =INDIRECT(SUBSTITUTE(F1,” “,”_”)), where F1 is the cell which has the name of your defined range i.e. South America in this case while defining the drop down in cell A2.
Go to Data Validation
And insert the formula in the source bar in the List section of drop down A2 and press OK.
As you could see excel is interpreting South America as South_America which is the name range defined by us and populating the drop down in cell A2 , the name range’s corresponding value .
Hope this helped.
