Excel Magic Trick 519: SUBSTITUTE & REPLACE Functions

[@excelisfun]

You are welcome!

[@gerhardwolle5736]

wow, you put a ton of effort into these! Thanks

[@ozstarjoy]

Great video, hep me a lot ;) Thnx quick que: is there anyways that I can do Partial Replace or Substitute For ex. in your video Column A10 has 8333-93333-22 I want to Change first - with / and keep the last - as it is, so I need 8333/93333-22. I hope i could explain well. :) Is there any formula for that? Thanks in Advance :)

[@EZGuitarKhmer]

Hi, I have data as in below called table (A) contains 2 columns Table (A) column1 column2 a 1 b 2 c 3 In table (B), there are two column also (old word) and (New Word) Table (B) Old Word New Word abc 123 acb 132 bca 231 My question is how to write the formula in column "New Word" in table (B) to get result as i have shown? Thanks

[@titusdsouza544]

I have an excel data sheet in which there are more than 1000 words - Column A with old text and Column B with new text. Company provides me a paragraph where I have to replace old text with new text. Multiple substitutes can’t be used if it exceeds the limit. Is there a way out? Thanks for your help in advance

[@muhammed6005]

I need to change SJR to SSR and SKJ to SKK, that is replace J with the previous letter, how can i do that please help.

[@excelisfun]

Maybe: =SUBSTITUTE(A1,"block","") or =TRIM(SUBSTITUTE(A1,"block","")) and copy it down column.

[@megan5495]

I use Open Office on a Mac and don't have an F9 button. Any substitute?

[@11gavinp11]

Hi, I want to replace multiple words from the one cell Cell contains = "3.2.1 Loop: Header Interchange Control Header Section" I want to replace the following words with nothing. "3.2.", "Loop:", "Header", "Section" so the final result will be "1 Interchange Control" i want to achieve this in one single formula.

[@ScubaGuy7777]

How do I replace "October Sales" in my cell Z20 with "November Sales"? I want it to be a macro, so I can run it at the beginning of each month to increment months throughout the year please.

[@wk964]

Hi I need to use replace have 2400 Words "block" I wanna replace it with nothing and also i want to find the block and replace at the same time...can you help me with formula. Thanks

[@artisticmystic7164]

Find and replace does this without a formula. I need a formula that change every Circle K D2 transaction in categories G2 from Personal spending to Gas, same with ATM D3 transaction from Personal spending into ATM through out my expense report.

[@hashtagbusinesssolutions1538]

how to select the formula in the list using shortcut key?

[@someevil]

Hi Guys, I have the below cells in excel (over 3000 lines of them) and i need to make the "Ch.xxx" uniform. So every cell needs to be checked and replaced, so Affleck St needs to change from 'Ch.627' to be 'Ch.0627', and then Agonis street 'Ch.0' needs to become 'Ch.0000' Any ideas??? Affleck Street (Ch.627 Read Place - Ch.704 Pemberton Street) Agonis Street (Ch.0 Dryandra Way - Ch.78 Honeysuckle Street) Borella Road (Ch.1787 Bulky Goods Entry - Ch.1993 Yorrell Street)

[@sakurarestaurant2540]

Thanks how to do this? 01, 02 , 03 .. to A01, A02, A03......

[@ankursharma6157]

Thanks!

[@ankursharma6157]

A Token of My Deepest Gratitude 🙏🏿

[@excelisfun]

Dear Ankur, Thank you very much for your donation. It helps me to keep making videos : ) I would have replied earlier, but the comment did not come through to me until today. Sincerely, Mike Girvin

[@excelisfun]

Thank for your your gratitude : ) : ) : ) : )

[@excelisfun]

I do not not a good formula way. You should post to the Mr Excel Message Board: mrexcel[dot]com/forum If there is not a formula solution, I am quite sure that someone could supply an easy bit of VBA code (which I am not so good with). If I encounter this sort of problem I usually do Find and Replace.

[@excelisfun]

Something like: =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"3.2.",""),"Loop",""),"Header",""),"Section",""))

[@11gavinp11]

Thanks.. it works perfect.

[@stevennye5075]

excellent

[@excelisfun]

EXCELlent : )

[@M.A.007]

=TRIM(SUBSTITUTE(SUBSTITUTE(A1;"F";"V");"D";"T")) A1 = FARHAD => VARHAT Double-off the "SUBSTITUTE EXCEL function" by once writing this formula.

[@kuknisti]

Thanks.

[@excelisfun]

You are welcome!

[@manojrawat9939]

There is only 5 spaces not 9

[@aartisaxena1439]

Picture quatily is not good.