Friday, December 12, 2008

Find and replace utility for Excel

Do you have clients who persist in using Excel as a word processor just because they need to insert some tables in the document? This seems to be the favorite pastime of some of my Japanese clients. I have a feeling that they are given a crash course on using Excel as a word processor when they join a Japanese company!

I was given about 31 Excel files each with two or more sheets to translate. As expected, within each cell, the author had generously inserted hard returns to align all text within a specific column. The moment I looked at it I knew that I would be wasting precious time removing these hard returns and translating the text in a CAT tool.  To add to this burden, are changes to specific terms that arrive later from the client. How would you make find and replace phrases throughout 31 files x 3 sheet on the translated document? I scoured the Web, landed on an Excel MVP page and installed a free macro utility called FlexFind (thanks Jan Karel Pieterse, you saved me considerable time, and a fine bowl of steaming noodles and beer is on me if you visit me in Japan).

The macro installs easily as an icon in Excel and also appears as an item under the Edit menu. Here's what it looks like:


I ensure that the Acknowledge and Each Item boxes are ticked so that when I do the find and replace, I read and confirm that the macro does replace the terms I want to replace. This is a good macro to keep and use for Excel.

I do have problems yet in merging text spanning across multiple cells and cleaning up sentences with hard returns in the same cell. If you do know of any solutions, for these do write to me.

Have a great day!


Sarah Alys said...

Wonderful! I too have had clients with inexplicable love affairs with Excel. I'm looking forward to having this tool in my arsenal!

Buthus occitanus said...

In the Excel "Find and replace box", you can input a newline character in the "Find what" field with alt+010.

You can replace it with nothing or a space to remove it.