Find and Replace Query

Hi,

I'm using Office 2007 and have an Excel query.

I downloaded a sales report and where there is a description of the goods, I need to shorten it to two words but cannot seem to do this with Find and Replace.

For example I might want to replace these three descriptions :

A box of blue widgets
Twenty Five Blue Widgets from New York
Blue Widgets with a dash of green

with:

"Blue Widgets"

Essentially, is there a way of highlighting all the columns that contain a phrase, like "Blue Widgets"with other words at the beginning and end and then replace them with just the phrase "Blue Widgets"

I hope this makes sense

Hi

The following code is adapted from the VBA help example and the main point to note is LookAt:=xlpart, which checks for any occurrence of the string within the text.

Please Login or Register  to view this content.

Regards

Jeff

Hi Jeff,

Thanks for your response, I'm afraid I am very new to Excel!

I pasted "lookat:=xlPart" into a cell but nothing happened, as I say, I don't know what I'm doing.

Is there no simple way of doing a Find & Replace and stripping out blocks of text and replacing them with other words?

The way I've been doing it is to click Control+F (Find), then type in my phrase to find cells where it occurs and then click Control+V with my one word phrase.
Because there are hundreds of cells, this takes hours!

All the best,
Derrick

Originally Posted by martindwilson

use find replace
find
*blue widgets*
replace
blue widgets

That works perfectly!
So the * seems to be the key to it.

Cheers!
Derrick