Multiple Substitute Formula

[aequitas1903]

Hello Everyone,

I was just wondering if it is possible to substitute multiple characters at once with a shorter formula.
I am using a conditional validation which refers to a helper column. I have to use a helper column to substitute some characters because excel name manager don't let me use commas, slashes etc.

This is my formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F33;" ";"_");"-";"_");"&";"_");"/";"_");"(";"_");")";"_");",";"_")

I tried "AND" and "OR" formula but it doesn't work Because of variable values, I have to use a formula. Here is an example.

"Consumer & Industrial Products & Services (CIPS) " this should look like this "Consumer___Industrial_Products___Services__CIPS_"


Your help is always appreciated.

Kind Regards

 

[JackHoff]

It appears that after Consumer and after Products, you have 3 _ _ _ underscores side by side and after Services, you have 2, would you like only one underscore after each word to be programmed in OR not important? There are other special characters that MS does not like to use in a file name, would you like to have all of them as part of the new formula or function?

 

[aequitas1903]

Hi JackHoff,

I am not having problems with underscore counts. But as you suggested I would like to change every special character to underscore. The formula is created by personal experience by me trying again and again A formula to replace all special characters to underscores would be great.

Thanks

 

[Aladin Akyurek]

See MSUBSTITUTE here:

http://www.mrexcel.com/forum/excel-questions/16364-how-remove-numbers.html

 

[aequitas1903]

ADVERTISEMENT

Hi Aladin,

The file will be used from other users. And I shouldn't make it an .xlsm file. I was just looking if there is a formula in excel that I don't know. Guess there is no formula for this and we have to write a code to do such thing.

Thank you Aladin and JackHoff for you interest.
Have a nice day
Regards

 

[JackHoff]

Your FUNCTION of nested Substitute functions cannot be simplified as Substitute is not an Array function. Do you mind changing to a VBA program instead of multiple functions like CLEAN, TRIM, FIND & REPLACE and SUBSTITUTE to get what you need? Have you written Macros before, are you familiar with the process? OR you can do multiple functions in multiple cells to get an end result as listed in CAPS here.

 

[aequitas1903]

ADVERTISEMENT

Hi JackHoff,

I am a little bit familiar with VBA but I am creating an excel file for data entry on a daily basis and so I have to use a formula in a hidden column. The best solution I come up with is as above (substitute in substitute). It was just my curiosity

Thanks again.

 

[JackHoff]

OK then, you are left with creating your own UDF, aequitas1903 or what ever name you wish to give it, when you write the Macro normally it will start with SUB and end with END SUB but you change SUB to FUNCTION and after you create the FUNCTION it will be listed with all the other functions that Excel gives you like SUBSTITUTE or CONCATENATE. You click on the cell you want to put the fx into, type =aequitas1903() and between the (), put the cell reference you need and pull the fill handle down as many rows as you need for todays work just like normal. Here is some help, I think.

http://www.mrexcel.com/forum/excel-...ing-special-characters-column-excel-file.html POST #6
http://www.mrexcel.com/forum/excel-questions/16364-how-remove-numbers.html UDF =MSUBSTITUTE() see how the sub was changed to function
http://www.mrexcel.com/forum/excel-questions/197984-removing-preceeding-space-number.html POST #6 another idea, I would move TRIM down one line.

Code works from top to bottom, CLEAN will remove most special characters but may leave a space, then do TRIM to clean up the extra spaces or move TRIM all the way to the last line of code, that is what I do anyways, MS is not perfect, still. Then you can SUBSTITUTE spaces into underscores and only have one between words.

If you dont want to do a UDF then do multiple functions in multiple hidden columns then, copy and paste special 'VALUES' into your required cells, good luck. OR nest more functions into a very long function as you have done.

 

[aequitas1903]

Thank you very much JackHoff. It really helped me to think different. I will try to figure something out.

Best Regards