Replace "illegal database" characters with "_" in one go

Hi,

I have a big list of the full path to files that we need to make into only alphanumeric with only "_" so that they "definitely" work in databases. So
P:\2012\367_mapping\Input\02 Culverts (minor)\Travelling Dairy Creek Road & Mode Road\Other Information\44353 - Travelling Dairy Creek Road.pdf
will become
\02_culverts_minor\travelling_dairy_creek_road_mode_road\other_information\44353_02_travelling_dairy_creek_road.pdf

etc.

I can run a =SUBSTITUTE(C1,"&","_") for each illegal character but was wondering if I can simply give it a list of illegal characters (,),~,` etc to replace with

or

The easier option would be to define that each character should be in [a...z], [1..9] and if not these replace with '_'

I would have hoped the CLEAN function does this but that is not it's purpose.

Any ideas?

how about something like this

Formula:

Please Login or Register  to view this content.

Thanks but I have to then repeat each illegal character twice. Can't it be from a vlookup or similar, so all characters specified in a volookup are replaced with "_" if found or ideally characters that are not in the set [a...z], [1-9] are replaced with '_'

So all the following and more are not allowed

`~!@#$%^&*()-+=\|]}[{;:'"?/.>,</*

Try this Macro.
It replaces all special chrs By "_" in the activesheet.

Please Login or Register  to view this content.