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.

 

[njimack]

Welcome to the board. Based on your sample data, the below should work:
=IF(ISNUMBER(MATCH(LEFT(A1,FIND(" ",A1)-1),{"The","Les","A","Le","La","An"},0)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)

 

[qetzel]

Welcome to the board. Based on your sample data, the below should work:
=IF(ISNUMBER(MATCH(LEFT(A1,FIND(" ",A1)-1),{"The","Les","A","Le","La","An"},0)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)

Dear Neil, That's absolutely fantastic! I've spent all day trying to solve this. Thank you so much

 

[njimack]

Dear Neil, That's absolutely fantastic! I've spent all day trying to solve this. Thank you so much

You're welcome! Thanks for the feedback.

 

[qetzel]

Dear Neil, That's absolutely fantastic! I've spent all day trying to solve this. Thank you so much

Actually this is a bit of a cheek on my part, but the icing on the cake would be if the final result was just the one word, i.e:
start with:
The man in the moon
Les Miserables
Rage over October
A slight affair

and I finish with:
man
miserables
rage
slight

(the whole object of the exercise is so I can go on and create meaningful filenames with these words).
Sorry - should have posted the whole problem straight up.
Thanks again

 

[njimack]

Actually this is a bit of a cheek on my part, but the icing on the cake would be if the final result was just the one word, i.e:
start with:
The man in the moon
Les Miserables
Rage over October
A slight affair

and I finish with:
man
miserables
rage
slight

(the whole object of the exercise is so I can go on and create meaningful filenames with these words).
Sorry - should have posted the whole problem straight up.
Thanks again

It's doable, but it's getting quite messy and a UDF would be more efficient than native functions. But since you stipulated no VBA...

=LOWER(IF(ISNUMBER(MATCH(LEFT(A1,FIND(" ",A1)-1),{"The","Les","A","Le","La","An"},0)),IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,MID(A1,FIND(" ",A1)+1,FIND("#",SUBSTITUTE(A1," ","#",2))-FIND(" ",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1))),LEFT(A1,FIND(" ",A1)-1)))

 

[XOR LX]

Or, if you're using 2010 or later:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",161)),161*(IFERROR(AGGREGATE(15,6,SEARCH(" "&{"The","Les","A","Le","La","An"}&" "," "&A1&" "),1),0))+1,161))

Regards

 

[qetzel]

Or, if you're using 2010 or later:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",161)),161*(IFERROR(AGGREGATE(15,6,SEARCH(" "&{"The","Les","A","Le","La","An"}&" "," "&A1&" "),1),0))+1,161))

Regards

Hi Neil and XorLX, just want to say thank you both so much for your help. I have to go out tonight, but I'll try these out first thing tomorrow. Thanks again

 

[qetzel]

Hi Neil and XorLX, just want to say thank you both so much for your help. I have to go out tonight, but I'll try these out first thing tomorrow. Thanks again

Hi again, rushed in this morning. Both formulae are brill, but neither completely perfect (Neil's fell over if cell didn't start with one of the forbidden words, whereas Xorlx's fell over if a forbidden word cropped up halfway through a cell). Since I could work out how to solve Neil's with a simple IFERROR statement, I went with that one. But thanks again to you both and thanks for introducing me to the Aggregate function, XorLx.
Best


Steph

 

[njimack]

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.