Find and Replace a specific area of a cell

blane

New Member
Joined
Sep 29, 2006
Messages
2
Hello.

I'm trying to create a macro that runs a find and replace within a column in my spreadsheet...

I'm hoping to use Excel to go through column "A", find all the words "THE" and replace them with "The"... BUT - here's the snag:

The column contains book titles and I only want to change "THE" when it happens to be the first word in the cell.

For example:

"THE LUFTWAFFE IN THE WEST 10-20 MAY 1940"

Due to the way I want to display this in another program, I want to replace the first "THE" with "The".. but not the "THE" found later in the title.

If anyone could come up with a solution to this problem it would be much appreciated and save me a lot of time. :cool:

Thank you.

-Blane
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hi, blane, welcome to the board

I know you asked for a macro but you could also use a formula approach quite easily...

put this formula in B1 and fill down

=IF(LEFT(A1,4)="the ",REPLACE(A1,1,3,"The"),A1)

Select column B, copy, edit > paste special > values
Delete column A
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,148
Members
410,666
Latest member
Al3cs
Top