0

I have a column that contains various international addresses now always formatted properly like a US address they vary widely but the common thing is the city names are all CAPITALIZED. Although within the cell I do find words in Proper case the only element that's fully CAPITALIZED is the city and I'd like to somehow use that so I can separate the CITIES from the address column into their own column.

Does anyone have a method to do this? Cities might have two separate capitalized words IE: NEW HAMPSHIRE and might now always be on the right of the column. I've considered using a RIGHT/LEN function but that doesn't work either.

Sample Data:

Mactan International Airport road Yusok Lapu Dapu 2014 CEBU
Hhesemetyfvskoy highway 35 148726 KHIMKI
Ueedamastrasse 3 8348 PFAFFIKON
Avenue Augine- Cittard, 40 1206 GENEVE
Bleiwyisova Yesta 20 5000 LJUBLJANA

Any idea's welcome!

UserSN
  • 953
  • 1
  • 11
  • 33
  • 1
    You might find this thread helpful https://stackoverflow.com/questions/71457207/how-to-extract-the-capital-words-or-block-letter-words-from-a-string-in-excel – Hooded 0ne Feb 07 '23 at 22:57

1 Answers1

1

With Office 365:

=BYROW(A1:A5,
    LAMBDA(z,
        LET(r,TEXTSPLIT(z," "),
            TEXTJOIN(" ",TRUE,
                FILTER(r,BYCOL(r,LAMBDA(a,AND(ISERROR(--a),EXACT(a,UPPER(a))))))))))

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81