This is a short story of mine, and an imaginary conversation in my head…
How to change the first letter (of first word only) to upper case in Excel?
This was a question from a colleague sitting opposite to me. My quick response was: “Yes… but it is a bit, just a bit complicated…”As I was doing something else, I didn’t answer him right away. Then after a while, he told me he “googled” the formula he needed. That’s fine.When I finished my task, I asked him for the formula he’s got… then I provided him my formula (a shorter one). :PThat’s the end of the story, but the beginning of my thought:
Is it good to have an Excel “nerd” sitting around you?
Most people would say YES, I guess, because they can have quick answer to their Excel questions.However, I think the opposite. It may indeed slow down your learning curve in Excel.Indeed what I meant “complicated” in the beginning is that it requires nested functions. The solution is not complicated. We just need to know three functions:
- MID (or REPLACE, that gives you a shorter formula)
Looking back, if I was not occupied by other tasks, I would continue our conversation like the following:
Colleague (C): Can we turn only the 1st letter to upper case?
Me: Yes. Of course.
C: Really?!… Would you tell me the functions or formula?
Me: Do you know UPPER?
C: Yes. But I need only the first letter, not all texts in a cell.
Me: Great, you do know UPPER.
How do you turn all texts into UPPER case in A1 then?
C: That’s easy.
Me: Cool. Then do you know LEFT?
C: Yes. That’s a basic function. I can get a particular portion of a text string, starting from the left.
Me: Then how do you get the first letter?
C: Another easy stuff.
Me: Exactly. Give you a tip, you may skip the second argument,
if you want to extract the 1st letter from left.
So the formula can be shortened to
C: That’s cool.
Me: Yes. It is.
Now you should be able to turn the 1st letter to UPPER case,
C: Oh, yes! As simple as this:
Me: Bingo! You solve your problem by 50% already.
How do you keep the rest of the text unchanged then?
C: …… That’s a challenge to me…… 😦
Me: Do you know REPLACE?
C: Not really…
Me: Since you know LEFT,
I guess you should probably know RIGTH and MID… do you?
C: Yes. I know both RIGHT and MID, and also LEN. These are basic functions for text.
Me: Wonderful. How do you get 3 letters starting from the 2nd letter?
C: Using MID can do that, like
Me: Bravo. You know functions well!
So how do you get all the letters starting from the second letter?
C: I can use a big number in the third argument. Depends on the length of the text, but in most of the case, 99 would do. So the formula should be:
Me: A tip for you to make this formula more robust,
in case you have more than 99 characters in your text,
you may replace 99 with LEN(A1), i.e.
C: Yes. Nice tip. 🙂
Me: Now you have the first letter in UPPER case, and the rest of the text…
C: … I can join them together with ampersand &.
=UPPER(LEFT(A1)) & MID(A1,2,99) or =UPPER(LEFT(A1)) & MID(A1,2,LEN(A1))
Me: Exactly. See! You knew the solution.
You don’t need to seek help for this.
C: Oh yes…. Thanks for guiding me.
Me: You are welcome! 🙂
The End of my imagination…
In many cases, you know the functions you need to solve your problem. You just need to go through the thinking process and connect the dots. Remember,
You are smarter than you are.
Want to learn more about text functions? Check these out.
If you know REPLACE, you should be able to modify the solution with REPLACE. Give it a try! Post your solution in comment.
what about using auto correction? There is a setting: Start each sentence with a capital letter. So you don’t need to think about complicated formulas. Although PROPER would have been my first thought, too when I read your post last night. But using this really depends on cell content.
Hi Sabine, this is a good idea for inputting.
However I don’t know why this autocorrect option never happened in my PC… I checked the options; all other Autocorrects are ok, but not this ☝️… really have no idea.
Btw, this situation discussed is mainly coming from data downloaded somewhere else. So autocorrect won’t work.
Having said, your suggestion is a good one as you raise the awareness of another undervalued Excel feature. 👍🏻
To be more precise, the autocorrect doesn’t work for the first word in a sentence as Excel interpretation for that autocorrect is based on a full stop.
I have revised the content to make it more clear. 😁
Maybe I didn’t make it clear.
We want just the first letter in the a sentence… so PROPER doesn’t work for this case.
Thanks for your input.
Thanks for the clarification, MF. UPPER(LEFT(A1) is indubitably the best solution. Another way to achieve the same result is UPPER(CHAR(CODE(A1))), which in inferior for being a longer formula but demonstrates the fact that a single problem can be solved in many ways. We just have to select the best solution, as mentioned.