Find the first two Uppercase letters in a string and return that value

Enter formula in B1 and copy down

Formula:

Please Login or Register  to view this content.

v	A	B
1	Kansas City KS	KS
2	Denver CO company name	CO
3	Salt Lake City UT companyname	UT
4	Southern Cal
5	Fairfield OH RET/SUPP CHN	OH
6	St. Louis MO	MO

Originally Posted by CrazyPerson

It would be useful to figure out to find the two uppercase letters in the string

with AlKey formula:

Formula:

Please Login or Register  to view this content.

but it's not solution for : Kansas City ab KS

==
With UDF VBA regex (for now and future):

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5" (IMPORTANT!)

• Select "Developer" tab
• Select "Visual Basic" icon from 'Code' ribbon section
• In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
• Select "References"
• Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
• Click "OK"

Step 2: Click on Insert Module. If you give your module a different name make sure the Module does not have the same name as the UDF below.
Step 3: In the big text window in the middle insert the following:

Please Login or Register  to view this content.

Step 4: Save and close the Microsoft Visual Basic for Applications Editor window.
usage: =regex(A1,"your pattern")

in this case:

Formula:

Please Login or Register  to view this content.

with this code you can define much more patterns not only as simply as ([A-Z][^a-z]{2})|([A-Z]{2})$")