Automatically replace a group of characters with a specific character

[merQrey]

Hello dear excel masters

hope someone can assist me with this query. i have a list of product codes that have certain characters that i would like replaced to the "_" character. this following items i would like to automatically be changed to the _ (underscore) character
"."
"/"
"+"
" " "
" space character "

so for instance if i have the following product code
"NUA-MPRO1866R/16G"

i would like it to be "NUA-MPRO1866R_16G"

some product codes have multiple characters that i would like changed
example as follows
"VORTEX V4 404/WU"

this should become "VORTEX_V4_404_WU"

can this be done??

 

[DRSteele]

This solution looks awkward and ugly but is necessary because REPLACE and SUBSTITUTE won't rationalise the array of search values (i.e., the ".", the "+", etc.) to a single result. I hope this isacceptable for your purposes, otherwise VBA will be required.

Simply nest each substitute construction inside another for as many values you wish to substitute. I had trouble understanding what you meant by the three """ so could you please elaborate?

P.S. Please don’t type posts in all lower case (especially the pronoun I) – it’s really frustrating to read and looks entirely unprofessional. Thanks tons.

	A	B
NUA-MPRO1866R/16G
VORTEX V4 404/WU

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #E2EFDA"]NUA-MPRO1866R_16G[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA"]VORTEX_V4_404_WU[/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".","_"),"/","_"),"+","_")," ","_"),"""","_")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","_"),"/","_"),"+","_")," ","_"),"""","_")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>

 

[Joe4]

You could simply use FIND/REPLACE, and replace ALL (so you would need to do one replace for each different character). That doesn't involve any formulas.

If you want it done automatically, you can use the Macro Recorder and record yourself performing those steps, and you will get the code you need.

If you wanted to get really fancy in VBA, you could set up an array with your different values and loop through them, but for only 5 characters, it won't save much (though the code is a bit shorter). You may also need to add some error handling, essentially telling it to ignore errors (like if it does not find any matches).

 

[Joe4]

P.S. Please don’t type posts in all lower case (especially the pronoun I) – it’s really frustrating to read and looks entirely unprofessional. Thanks tons.

Not nearly as bad as typing in all caps. Personally, I don't have any issue with it, but I guess we all have our pet peeves, eh?

 

[DRSteele]

It's not really a peeve nor a pet peeve. Requiring no less from others than of myself, I think we all owe one another the courtesy of being clear, precise, accurate and correct in our communications. Developing habits of excellence can only serve to improve us all. Heck, that's why we participate in this awesome forum --- to help one another improve --- with myself being the most gracious beneficiary of everyone's efforts.

TYPING IN ALL CAPS OUGHT TO BE MADE ILLEGAL, PUNISHABLE BY BANISHMENT FROM THE INTERNET.

 

[merQrey]

Thank you kindly for your assistance DRSteele
I take heed of your note and do apologize for any inconvenience caused.
Thank you once again for your assistance, greatly appreciated

 

[DRSteele]

You're most welcome. No apologies necessary...I shouldn't have mentioned it. I'm happy to help.