Hello,
I want to check for 2 consecutive upper letters in a cells text, in order to extract the upper text after the upper letters.
Example: In cell A1 i have the following text: " Advance Payment FOR JOHN SMITH ". All my data follows this pattern. I want to extract " FOR JOHN SMITH" from that cell.
I found and modified a formula the check for upper letters and join them with spaces between them:
=TEXTJOIN(" ",TRUE,TRIM(IFERROR(INDEX(MID(A1,ROW(INDIRECT("$A1:$A"&LEN(A1))),1),IF(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("$A1:$A"&LEN(A1))),1)),ROW(65:90),0)),ROW(INDIRECT("$A1:$A"&LEN(A1))),"…")),"")))
But this returns me : "A P F O R J O H N S M I T H"
I dont want the first 2 upper letters, that is why i need to check for 2 consecutive upper letters.
PS: I dont need vba codes, this needs to be used by users with basic skills, they wont use macros
Bookmarks