Convert formula to a Macro

I'm using this formula =IF(LEFT(A1,3)="The",RIGHT(A1,(LEN(A1)-4))&" (The)",A1) to convert any string starting with 'The' as follows:

The Long and Winding Road
Tired Feet
The Highest Mountain

to

Long and Winding Road (The)
Tired Feet
Highest Mountain (The)

I want to be able to convert this into a macro, but I'm not sure how to go about doing that.

Can anyone help, my knowldge of VBA is extremely limited.

Hi JieJenn,

That works well thank you, though what I really want to be able to do is to run a Macro rather than a UDF, so I don't have to enter the function in a second column. I want to be able to run a macro over the column containing the source text and for the macro to amend the text accordingly without having to insert a new column and then copy and paste special the new text as a value, if that makes sense. I have a large worksheet and I am trying to avoid having to 'move' any data where possible.

This updates data in Col.A

Please Login or Register  to view this content.

Hi jindon,

That is absolutely perfect and does exactly what I need it to.

Many thanks for your help

Hi xladept,

Okay, I didn't realise you could run functions as Macro's. I'll give that a try as well.

Many thanks