How to Apply Proper Case Without Changing Existing Lowercase Words in Excel
Question details
The user needs to capitalize normally formatted words in a cell while preserving words that were intentionally typed in all lowercase or uppercase.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and standardizing text data in a spreadsheet where specific acronyms or stylistic lowercase words must remain unchanged.
- Observed behavior
- Using the standard PROPER function forcefully alters all text, changing uppercase acronyms and lowercase words to a capitalized first letter with lowercase subsequent letters.
Verify that your version of Excel supports dynamic array functions such as TEXTSPLIT and LET, which are available in Microsoft 365 and Excel Online.
Use a Dynamic-Array LET Formula
This method evaluates each word individually using modern dynamic array functions, making it perfect for Excel Online and Microsoft 365 users who cannot use macros.
By combining LET, TEXTSPLIT, EXACT, UPPER, and PROPER functions, you can instruct Excel to split a text string into individual words, check the casing of each word, and apply the proper case only where appropriate.
This completely bypasses the need for complex VBA macros, which are unsupported in web versions of Excel.
Click on a blank cell where you want the newly formatted text to be displayed.
Type the formula: =LET(t,TEXTSPLIT(A1," "),e,EXACT(t,UPPER(t)),p,IF(e,PROPER(t),t),TEXTJOIN(" ",TRUE,p)) (assuming your original text is in cell A1).
Press Enter to execute the formula. If you have multiple rows of data, click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the remaining rows.
Format Text Easily with WPS Spreadsheet
WPS Office provides a highly capable spreadsheet application that fully supports advanced text manipulation functions. You can clean up, format, and standardize your data exactly how you want without paying for an expensive subscription.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the file containing the text data you want to format.
- 2. Insert the formula: Select a blank cell adjacent to your original text and paste your chosen text manipulation formula.
- 3. Apply to the dataset: Press Enter to see the result, then double-click the fill handle to copy the formatting rule down the entire column.
Frequently Asked Questions
Why does the standard PROPER function change my acronyms?
The standard =PROPER() function is strictly designed to capitalize the first letter of every word and force all subsequent letters into lowercase. It lacks the logical conditions needed to recognize and preserve existing acronyms or lowercase words.
Can I use this formula in older versions of Excel?
No, functions like TEXTSPLIT and LET are modern dynamic array functions available only in Microsoft 365, Excel Online, and recent updates of alternative suites like WPS Office. For older versions like Excel 2016, a VBA macro is required.
Will this solution work on Excel for Mac?
Yes, provided you are running the Microsoft 365 subscription version of Excel for Mac. The dynamic array functions used in the formula are natively supported in current Mac environments.
