Excluding text up to the last capital letter from the right

Hi all,

I have had some great help in the past, I’m afraid I need some more…… I have been using the formula below, which will extract the first word up until a 2nd capital letter, for instance ThomsonTravel and it will return Thomson, great. But it does not pick up anyone with a double barrelled name or a name with an apostrophe in it, so I need to tweak the formula but I can’t manage it. What I need it to do is to exclude everything up to the first capital letter starting from the right, so then any funny names shall be picked up and the mode of transport excluded.

{=TRIM(LEFT(N6,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),"x"&MID(N6,2,999)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),1)-1))}

Really appreciate any help with this.

Cheers,

Hi all,

Thanks very much for the replies, they are very detailed I will try them out.

Admin - Apologies, I admit I didn't read the rules and I posted this question on another excel forum. Won't do it again, honest guv!