Excel Magic Trick 519: SUBSTITUTE & REPLACE Functions

Thanks!

Picture quatily is not good.

Sir please give me your email address I have many doubt and I want to share with you.

excellent

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.

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.

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

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

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

There is only 5 spaces not 9

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

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

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 :)

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

I'm pretty sure I'm lost. Maybe someone can help... I want to extract fax numbers only from a cell that contains "Phone: 555-555-5555 Fax: 555-123-5555"
of course each number is different but I need to extract for hundreds of columns....
Can someone help? IF SO, THANK YOU "!!!!!!"

Thanks.

Thanks.. it works perfect.

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

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.

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)

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

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

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.

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

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.

You are welcome!