First instance of consecutive upper case letters in a string

[Healthydogs]

I need to be able to identify the position within a 65 character string of the first instance of consecutive upper case letters.

The first instance can occur anywhere in the string and are 2-4 random characters. There is always a space before each occurrence.

I intend to remove all text to its right to include the blank space just before it.

I want to make use of built-in Excel functions/formulas.

I’ve tried various combinations of find/search, upper(), trim, and all I get are errors.

Any suggestions, please?. I’ve very little hair left to pull out…

 

[Cubist]

Can you post some examples and expected outcome?

 

[Eric W]

Welcome to the MrExcel forum!

See if this works for you:

Book1
	A	B	C	D	E
1	abc123 JK xyz 77				8
2	cd BDAC 33445 alskdfjalsdkjfalskdjf CDFA asdf				4
Sheet5

Cell Formulas
Range	Formula
E1:E2	E1	=AGGREGATE(15,6,FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}&{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},A1),1)