Changing Cases and Spaces in Excel
For reasons that probably make sense to its creators, Excel does not have the “Change Case” command that we love in Word. Neither does Apple’s “Numbers.” If you have a long list of names, for example, and want to change them all to proper case, no single magic-wand command will make that happen. However, there is a surprise: you can do it with a formula.
Insert a blank column next to your column of names. If cell A1 reads:
malvinia mysteriosa
enter this formula into cell B1: =PROPER(A1)
Like magic “Malvina Mysterioso” will appear in column B1. Drag your formula all down column B and hide Column A. If you delete it, column B will have nothing to refer to and will get hysterical. Just hide it.
You can use LOWER to make it all lower case and UPPER to make it all upper case…seeing a pattern here? You can even remove extra spaces. That command is TRIM. If cell A2 said:
Joe Whatshisface
In cell B2 I would enter:
=TRIM(A1)
He’d still have a ridiculous last name but there wouldn’t be any excess spaces.
If I wanted the whole column to remove the extra spaces and make the names proper case, I would enter the following formula into cell B1 and drag it down the whole column:
=TRIM(PROPER(A1))
or I could type =PROPER(TRIM(A1))
It doesn’t matter which command comes first. This works in either Excel or Numbers.
Of course I probably wouldn’t do that. What I would probably do is this:
- Find and Replace
- Enter two spaces into the “Find” box”
- Enter one space into the “Replace” box
- Replace all
- Repeat until all the spaces are gone.
I’m not saying it’s efficient, but it’s what I’d do. It goes fast and while it’s not as neat, it seems less labor-intensive on a large spreadsheet. That’s just preference, though.
What I’d probably do for changing the case unless the spreadsheet was positively enormous is copy and paste it into Word or Pages, select the column, and choose “Change Case.” Then I’d copy and paste the whole thing back in Excel or Numbers. But I kind of love that people who think in terms of numbers figured out how to do formatting using a formula, and it would be a cool trick for a big spreadsheet.
Reversing Last Name First Lists into a Single Excel Column
Counting Multiple Variables in Excel
Everything Pasted into One Cell!
How to View Excel on Multiple Monitors
Clicking these links helps support this site and adds nothing to the great price:
