Stripping out certain words from start of cells

[qetzel]

Hi
I'm new to this forum, and hope someone might be able to help me. I have a range of about 5000 rows, each representing a title of an article, and I need to be able to strip out the words "The ", "Les", "A ", "Le ", "La " or "An " if they are at the start of each title.

So e.g. I start with:
The man in the moon
Les Miserables
Rage over October
A slight affair

and I finish with:
man in the moon
miserables
rage over October
slight affair

I've faffed around with mids and finds and substitutes but am running into problems because the strings I want to replace are all of different length. I'd be very grateful for any help, ideally which doesn't involve macros.

 

[qetzel]

Glad you got it working, but my formula should work regardless. The first IF statement tests the text before the first space. If this isn't equal to one of the forbidden words, it returns the first word.

Hi Neil, oh yes I should have examined it more closely: in fact it was falling over because it started with a space - some of the cells weren't TRIMMED I realise now...
Thanks again - I will certainly use this great forum again!