Find and replace script / macro?

**luu980** · 06-22-2008, 09:33 AM

Help Needed: Find and replace script / macro?

Hi,

I'm not sure what type of function is needed for what I'm describing below. I'm thinking it is a script or macro? I don't know anything about writing scripts or macros and I am quite new to excel. I'm hoping someone can help.

I'm trying to learn chinese and am making chinese flashcards for myself.

The fact that some of the cells have chinese characters probably doesn't matter, but I just want to be thorough in my description.

Example:
A---------------B---------------C
dog-------------gǒu-------------The dog is in the house. Na ge gǒu zai jiālǐ.
家裡-------------jiālǐ-------------The dog is in the 家裡. Na ge gǒu zai jiālǐ.

I need for the script to take the value of Cell in Column A "dog" and the value of Cell in Column B "gǒu" and replace those with " ~ " in Cell C.

Thus, after running the script/macro the output will be:
A---------------B---------------C
dog-------------gǒu-------------The ~ is in the house. Na ge ~ zai jiālǐ.
家裡-------------jiālǐ-------------The dog is in the ~ . Na ge gǒu zai ~ .

I will be eternally grateful for help on this!!

P.S. - the formatting for the tabs look strange (i used ---- instead). I'm also attaching a sample of the excel file so that it can be read more easily.

**Simon Lloyd** · 06-22-2008, 11:11 AM

Perhaps this will suffice:

Please Login or Register  to view this content.

**shg** · 06-22-2008, 12:37 PM

Simon's code replaces every instance of dog in column C with a tilde, rather than just the instance on the same row. If that's not what you want, then change the line to:

Please Login or Register  to view this content.

**Simon Lloyd** · 06-22-2008, 12:42 PM

Well caught shg! i assumed that the user was going to have a complete list in column A & B but good thinking!

By the way i forgot about blanks with my "blanket" code so:

Please Login or Register  to view this content.

**luu980** · 06-24-2008, 01:11 AM

Thank you for the code. I'm going to try it out asap!

Ok, this is a really really stupid question I know. I am a total beginner to excel. How do I implement this code? I'm using excel 2007.

*Sigh*, very sorry about this question

**Simon Lloyd** · 06-24-2008, 06:18 AM

Hold Alt and press F11, on the left hand side you see your worksheet names and ThisWorkbook right click there and choose INSERT, MODULE and paste thye code in, you can then run it from the worksheet by selecting VIEW in the menubar then on the left MACROS then choose the macro you want to run.

**luu980** · 06-30-2008, 04:44 AM

Hi Simon,

Thank you very much for the help. Sorry for the late reply, I fell ill a few days.

I tried out the script and it appeared to work perfectly for small sample size of my spreadsheet.

However, when I tried to use it for my entire large spreadsheet of 21,000 items, a strange thing happened. It took several hours(!) to run and when it completed it replace many of the data in Column C with '~' when it should not have.

Very strange...

Example item:

Original item:
等-----------děng-----------v. wait | Qǐng děng yīhuǐr. Please wait a minute. ◆b.f. ①class; grade (頭等 tóuděng attr./n. first class/rate) ②be equal (平等 píngděng s.v. equal ; n. equality)

Replaced item should be:
等-----------děng-----------v. wait | Qǐng ∼ yīhuǐr. Please wait a minute. ◆b.f. ①class; grade (頭∼ tóu∼ attr./n. first class/rate) ②be equal (平∼ píng∼ s.v. equal ; n. equality)

Resulting output:
等-----------děng-----------v. w ~ it | Qǐng ∼ yīhuǐr. Ple ~ se w ~ it ~ minute. ◆b.f. ①cl ~ ss; gr ~ ~ (頭等 tóuděng ~ ttr./n. first cl ~ ss/r ~ te) ②be equ ~ l ( ~ 等 ~ s.v. equ ~ l ; n. equ ~ lity)

hmmm...

I'm not sure why this happened, but is there a way to fix this?

**Simon Lloyd** · 06-30-2008, 04:51 AM

The code is supposed to replace items in column C

**luu980** · 06-30-2008, 10:53 AM

Yes, it's supposed to replace the items in column C, however it is replacing the wrong items.

It is replacing with the Resulting output (in column C) is:
等-----------děng-----------v. w ~ it | Qǐng ∼ yīhuǐr. Ple ~ se w ~ it ~ minute. ◆b.f. ①cl ~ ss; gr ~ ~ (頭等 tóuděng ~ ttr./n. first cl ~ ss/r ~ te) ②be equ ~ l ( ~ 等 ~ s.v. equ ~ l ; n. equ ~ lity)

but the replaced item in column C should be:
等-----------děng-----------v. wait | Qǐng ∼ yīhuǐr. Please wait a minute. ◆b.f. ①class; grade (頭∼ tóu∼ attr./n. first class/rate) ②be equal (平∼ píng∼ s.v. equal ; n. equality)

I'm not sure why this is happening. In small excel file with a few rows the macro works without any problems, but for my spreadsheet of 21,000 items, almost all of the replaces in Column C is replaced incorrectly.

**Simon Lloyd** · 06-30-2008, 11:52 AM

Im not sure why that is replacing all the letter a' in the text, can you please post a workbook with many more examples for me to work with, it would be even better if you could suply the actual worksheet!

**luu980** · 07-01-2008, 10:46 AM

Hi Simon,

I have included a sample of my excel worksheet. The original file is about 11mb, so I only included 100 items. The original and the output.

If it is not enough please let me know, I can provide more examples, if you need the whole worksheet, I can email that you to you also.

Thank you for your help!

**luu980** · 07-12-2008, 06:23 AM

Hi,

I'm still having problems with the find and replace mentioned above. Can anyone help me with this?

I'd greatly appreciate it.

**luu980** · 03-09-2009, 12:01 PM

I think the problem is solved.

The final code below appears to work correctly!:

Please Login or Register  to view this content.

Thank you so much for the help everyone!!

**Tarball** · 03-09-2009, 01:59 PM

If you find that you still have problems then see this thread: http://www.excelforum.com/excel-prog...character.html

Look towards the bottom to my post where you will find a Replace String function that you can manipulate and use. This has worked quite well for me on many projects.

Find and replace script / macro?

LinkBack

Thread Tools

Rate This Thread

Display

Find and replace script / macro?

Re: Find and replace script / macro?

Re: Find and replace script / macro?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions