How to exclude all text up to and including a capital letter from the right

[Folander]

Hi all,

Could realy do with some help. The below formula extracts the text up until the 2nd capital letter, like ThompsonTaxi and it will return Thompson, great. But it is not picking up names with an apostrophe or double barrelled names. I need a formula that starts from the right and will exclude everything up to and including the first capital letter, so removing Taxi from the above.

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

Cheers,

Fo

 

[Eric W]

Try:

	A	B	C
ThompsonTaxi	Thompson
MaryJoCookies	MaryJo
Frank'sAuto	Frank's

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]{=LEFT(A1,AGGREGATE(14,6,FIND(CHAR(ROW(INDIRECT("65:90"))),A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1))))),1)-1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Cross-posted here: https://www.excelforum.com/excel-ge...apital-letter-from-the-right.html#post4983957

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Folander]

Hi Joe4,

That s worked a treat, thanks very much I am very impressed!

Admin- Apologies for the double post, I wasn't aware the websites were connected. Lesson learned

 

[Joe4]

Admin- Apologies for the double post, I wasn't aware the websites were connected. Lesson learned

They are NOT connected. That isn't the issue. The issue is people unaware that the question has been posted (and maybe already solved) elsewhere, hence they would be wasting their time working on a problem that already has a solution.

Read this to get a full understanding of the concerns with Cross-Posting: https://www.excelguru.ca/content.php?184