US Trends

how to convert capital letters to small letters in excel with first letter capital

How to Convert Capital Letters to Small Letters in Excel with First Letter

Capital

Excel makes it simple to transform ALL CAPS text like "HELLO WORLD" into sentence case, such as "Hello world," where only the first letter is capitalized and the rest is lowercase. This is a common data cleanup task, especially for names, titles, or imported lists—think fixing customer data from a messy CRM export.

The go-to formula uses basic text functions to handle this precisely, ensuring consistency across your spreadsheet.

Step-by-Step Formula Guide

Use this formula in a new column next to your data (say cell B2 if your text is in A2):

=REPLACE(LOWER(A2),1,1,UPPER(LEFT(A2,1)))

Here's how it breaks down:

  • LOWER(A2) converts the entire string to lowercase (e.g., "hello world").
  • UPPER(LEFT(A2,1)) grabs and capitalizes just the first letter ("H").
  • REPLACE swaps the first character of the lowered string with that capital "H".

Drag the formula down for multiple rows—it auto-adjusts. Copy, paste values (right-click > Paste Special > Values) to replace originals permanently.

Original Text| Formula Result| Notes
---|---|---
HELLO WORLD| Hello world| Perfect for sentences 1
JOHN SMITH| John smith| Works on names too 3
excel tips| Excel tips| Handles mixed case 5

Alternative: PROPER Function (Title Case)

If you want every word's first letter capitalized (e.g., "Hello World"), use:

=PROPER(A2)
  • Great for headings or names like "John Smith."
  • But per your query, stick to the first-letter-only formula for true sentence case.

Pro Tip : For bulk apply, select your range and run this VBA macro (Alt+F11 > Insert Module):

Sub SentenceCase()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = WorksheetFunction.Replace(LCase(cell.Value), 1, 1, UCase(Left(cell.Value, 1)))
    Next cell
End Sub

Test on a copy first!

Quick Troubleshooting

  • Empty cells? Formula returns blank—safe.
  • Numbers/symbols? Ignores them, focuses on letters.
  • Non-English? Works for Latin alphabets; accents may need tweaks.
  • Excel 365? Flash Fill (Ctrl+E) auto-detects patterns after 2-3 examples.

Imagine inheriting a 10,000-row "SCREAMING" database—bam, polished in minutes. Trending on forums like Reddit's r/excel lately, as data imports explode in 2026's AI-driven workflows.

TL;DR : =REPLACE(LOWER(A2),1,1,UPPER(LEFT(A2,1))) nails it—lowercase everything, uppercase the first. Copy-paste values to finalize.

Information gathered from public forums or data available on the internet and portrayed here.