how to write a formula to change multiple alphabets in the same cell

Question

How do I write function for replace multiple alphabets in one cell, I want to change "space" to "%20", " ' " to "%27", "+" to "%2B"

an example as in the image below

@BigBen yes I have tried using substitute, but I don't know how to write 3 substitution functions in one cell. — Nata_The_Coco, Apr 19 '22 at 13:40
@SolarMike Previously I also used find and replace, but I'm tired because I have a lot of files. so if possible how can i find and replace in a different excel file — Nata_The_Coco, Apr 19 '22 at 13:42
Then consider a vba code to loop through the files doing the find : replace. — Solar Mike, Apr 19 '22 at 13:43
@Nata_The_Coco refer this link, as Scott Sir was mentioning about `REDUCE()` & `LAMBDA()` if you are on O365 then only [Complex_Substitute](https://stackoverflow.com/questions/70959273/complex-substitute-beyond-64-nesting-limit) — Mayukh Bhattacharya, Apr 19 '22 at 13:48
However as @BigBen Sir mentioned to nest `SUBSTITUTE()` is the easiest way to do, — Mayukh Bhattacharya, Apr 19 '22 at 13:50
@SolarMike one was confirming with Scott Sir, but sir deleted the comment so by that time now removed — Mayukh Bhattacharya, Apr 19 '22 at 13:51
Here is a possible dup, with vba UDFs https://stackoverflow.com/questions/68685282/can-you-use-substitute-for-many-values-without-nesting — Scott Craner, Apr 19 '22 at 13:53

score 2 · Accepted Answer · answered Apr 19 '22 at 13:55

2

Using nested SUBSTITUTEs:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","%20"),"'","%27"),"+","%2B")

answered Apr 19 '22 at 13:55

BigBen

Since you put this answer, I dupped this question as now it has just about all approaches covered. – Scott Craner Apr 19 '22 at 13:59
@ScottCraner Sir, there are other approaches as well, who knows it can be done with Power Query as well, ! – Mayukh Bhattacharya Apr 19 '22 at 14:01
1

@MayukhBhattacharya it can be done with powershell too, but those are outside the scope of this question as it asked for formula answers. The dup and this answer cover the formula tag. There may still be answers that use formula, feel free to add answers to the dup. – Scott Craner Apr 19 '22 at 14:06
Sir, why can't I use that function, when I write it, it doesn't produce results but only writes =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A$1," ","%20"),"'","%27"),"+","%2B") what's wrong with my excel, why it works for you it doesn't work for me – Nata_The_Coco Apr 19 '22 at 14:07
@Nata_The_Coco may the cells are formatted as TEXT change it to general – Mayukh Bhattacharya Apr 19 '22 at 14:07
@ScottCraner Sir, may be OP is not aware of Power Query, but PQ is very much part of Excel. But for me i would have followed what BigBen Sir shared or the one JvdV Sir, shared and now since you have shared with VBA its more than enough. – Mayukh Bhattacharya Apr 19 '22 at 14:09

1 Answers1