1

The PROPER function in Microsoft Excel:

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

I have a spreadsheet that has hundreds of rows, each row with dozens of columns, and I want to apply the PROPER function (or a similar "capitalize the first letter of each word, lowercase the rest" transformation) to every cell, in-place. When I'm done, the old (all uppercase) values are no longer needed--I only need the transformed versions.

Is there a straightforward way to do this?

Improve this question
4
  • 1
    not really an excel solution, but fast. [ Select All , copy > paste in Notepad++ | Select all > Edit >Convert Case to > Proper Case | Select All , copy > paste in excel ]
    – p._phidot_
    Commented Jun 14, 2021 at 22:04
  • 1
    another quick and dirty way: Suppose you have data in A1:FZ999. 1. In GA1 write =PROPER(A1); 2. copy/drag down and to the right until all your data is represented; 3. for the new range copy then paste values; 4. Delete original columns A:FZ
    – Alex M
    Commented Jun 14, 2021 at 23:03
  • Thanks @AlexM, that works, although it's not in in-place solution, and is a little cumbersome when dealing with very large CSVs. I decided to go with a Notepad++ pre-processing solution (open the CSV in Notepad++, CTRL-A, ALT-U, CTRL-S). Pretty straightforward, was just wondering if there was a way to do this entirely in Excel. I use Notepad++ quite a lot, though, so this will work for me.
    – Josh
    Commented Jun 15, 2021 at 13:39
  • That makes sense - not sure if Notepad++ was part of a good answer for you. I think you should write yours up as an answer and select it as the chosen answer, since it worked for you. Opening the CSV IN Notepad++ is a key element to a good solution here
    – Alex M
    Commented Jun 16, 2021 at 17:32

2 Answers 2

Reset to default
1

Not really an excel solution, but fast.

[ Select All(in excel) , copy > paste in Notepad++

| Select all(in notepad++) > Edit >Convert Case to > Proper Case

| Select All(in notepad++) , copy > paste in excel ]

Improve this answer
2
  • So this was something I experimented with, but I was concerned that in some cases the cell location would be lost, e.g., if cells contained tabs or other special characters. I wound up adopting a similar strategy with Notepad++, however: 1) Open CSV in Notepad++, CTRL-A, ALT-U (converts to "proper" case), CTRL-S. After that I can open the CSV in Excel and the case is as desired.
    – Josh
    Commented Jun 15, 2021 at 13:35
  • Congratulations, glad it solves.. ( :
    – p._phidot_
    Commented Jun 15, 2021 at 14:12
0

Try this formula:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

enter image description here

Improve this answer
2
  • Thanks. Unfortunately this isn't an in-place transformation, it gives me the new text in different cells.
    – Josh
    Commented Jun 15, 2021 at 13:33
  • Try to provide the sample and modify your question.
    – Lee
    Commented Jun 16, 2021 at 8:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .