Multiple Substitute Formula

aequitas1903

Board Regular
Joined
Mar 8, 2012
Messages
127
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:
Code:
=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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thank you very much JackHoff. It really helped me to think different. I will try to figure something out.

Best Regards
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top