How: PROPER capitalization on all cells except cells with consecutive capital letters

[solotak]

Hi all, was googling around for a solution to this.

Basically i have around 2000 cells in a column that has names of various capitalization formatting.

Ideally i just want to use Proper Capitalization for every first letter on every word. While PROPER works well, it also applies to names that are identified by capitalization.

eg. =PROPER(A1) makes ABC Company into Abc company.

eg2. =PROPER(A2) makes ABC123 Group into Abc123 Group

eg. =PROPER(A3) makes AB Property group into Ab Property Group

I'm looking to preserve the consecutive capital letters and then the rest of the name under the PROPER formatting.

Also the PROPER capitalizes prepositions (eg. Bank Of America). Anyway to prevent that from happening?


Any tips are greatly appreciated. Thanks

[martindwilson]

use a word processor,seriously try copying into word change case to title case copy back to excel
or
read here
http://www.dailydoseofexcel.com/arch...icrosoft-word/

[solotak]

Hi martindwilson thanks for your suggestion.

I checked out that page and couldn't find how that could preserve consecutive capital letters while executing PROPER for the trailing word(s). I saw a post somewhere here that looked somewhat similar what i wanted to do but it involved VBA/macro and other complicated formulas than what i'm trying to achieve. I'm just trying to create a rule that seeks cells with capital letters consecutively, and if it does, retains ABC but the following word(s) become (Proper). i.e "ABC Real Estate"

[martindwilson]

i said use TITLE case in word ,that preserves AAA
here's a way with loads of formulas!

[solotak]

martindwilson sorry i overlooked the title case instruction :o... Anyway i couldn't really figured and tried to decipher your xls template.

It looks fairly complicated and i just copied/pasted my company list to the A column and then dragged down Columns B to W so the formulas would go into the cells beneath and like magic all the names worked as described, even the prepositions!

Awesome work, i really appreciate you taking the time to help out, i just wish i understood how you formulated it.