Find and Replace a specific area of a cell

[blane]

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.

Thank you.

-Blane

 

[barry houdini]

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

 

[blane]

Thank you very much, problem solved!