Uppercase First letter of Each Word in String but Ignore Other Capital Letters

[nikolast]

I have done a lot of searching, but I have been unable to find an example formula how to have a string in one cell produce in another column the same string but with the first letter capitalized while ignoring other capitals in the string.

For example:
ACL Machine Alarm and Error Troubleshooting

I need this to read:
ACL Machine Alarm And Error Troubleshooting

in another cell. When I try using other formulas such as PROPER(), it will remove the other capital letters (i.e. above it would show up as Acl Machine Alarm...)

Thank you for your assistance!

[JohnTopley]

You will probably need VBA and a list of exceptions (ACL, IBM, NASA, ASCII etc) as there no other way of identifying the acronym

[nikolast]

Thanks for the quick reply. I was hoping there was a way to look at only the first letter and change if necessary. Sounds like VBA may be the only way to make that happen... bummer.

[JohnTopley]

If there is a limited list then a formula might be an option using a "lookup") BUT I suspect the acronyms are not limited to the first word (?)

[nikolast]

You are correct acronyms could appear anywhere. The goal was to just look at the first letter and capitalize if it is not.

[Rick Rothstein]

Since you are using a version of Excel with the CONCAT function... and assuming there are not leading punctuation marks for any of the words (in other words, a word won't have a opening parenthesis, dash, etc. in front of it) and that the only letter that needs to be looked at is the one after a space character... then this array-entered** formula should do what you want...

Please Login or Register  to view this content.

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

NOTE: This formula assumes none of your text will contain more than 99 characters. If they could contain more, than all three 99's will have to be increased to a number equal to or larger than the longest possible text string.

[nikolast]

This is perfect Rick. Thank you for your assistance on this.