Hi
I have a problem in creating dependent lists with validation, and in my filter, I have the Data Validation entry with space, or with "&", or with "-" , I used =INDIRECT(SUBSTITUTE(J9," ","_"), it works for the entry with space, but it didn't work for "&" and "-".
what I should do then?
thanks,
Yvonne
Hi Yvonne,
Welcome to our forum! Please attach a sample Excel file so we can see the whole picture of your formula and how it relates to the worksheet. Anonymise it if required.
Mynda
Hi Mynda,
Nice to e-meet you! Here is the workbook I refer to.
Hi Yvonne,
No attachment. There's a video here showing how to attach files
https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-this-first
Regards
Phil
HI
Try it again.
Hello,
In order to substitute several characters you need to nest the SUBSTITUTE function per character/word you want to change. You can read more about it in this blog post.
Example: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C9,"&","_"),"-","_")," ","_")
Br,
Anders