pull out ALL CAPS word

Hello forum users!

This is my second post, and the first resulted in much success, so I hope you can help me again!

I have a list of over 3000 file names, each of which are titles with one word in ALL CAPS. That word is supposed to be the 'sort word' by which the files are alphabetized. For example:

"Kendra Smith MONROE Award in Geosciences"

Is there any way to create a macro that will do two things:
(1) identify the ALL CAPS word in the string (in this case 'MONROE') and put it in the next column
(2) change 'MONROE' in the full name to Proper Case

This would save me SO much time. Any help would be hugely appreciated!

Thank you

I now see that the second step will be easy to do by just formatting the cell in proper case once the ALL-CAPS word is identified and copied into the next cell. But, it's the first step that's still giving me a hard time!

In VBA, you will have to insert a module (left click on ThisWorkbook, Insert, Module)

In Module code:

Please Login or Register  to view this content.

In adjacent cell (where you want the 'Monroe')

Please Login or Register  to view this content.

Then apply to the entire column by doubleclicking the bottom right corner of the cell.

I love you all. THANK YOU! It worked .

No problem, just remember to mark this topic as solved