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:

• UPPER
• LEFT
• 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:  

Imaginary conversation

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. 

 =UPPER(A1)

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.   

=LEFT(text,1)

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

=LEFT(text)

C:  That’s cool. 

Me: Yes.  It is.

Now you should be able to turn the 1st letter to UPPER case,

do you?

C: Oh, yes!  As simple as this:

=UPPER(LEFT(A1))

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

=MID(A1,2,3)

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:

=MID(A1,2,99)

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.

=MID(A1,2,LEN(A1))

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.

Be Confident!

BeExcellence!

🙂

Want to learn more about text functions?  Check these out.

• RIGHT, LEFT, MID
• UPPER, LOWER, PROPER
• REPLACE and SUBSTITUTE

If you know REPLACE, you should be able to modify the solution with REPLACE.  Give it a try! Post your solution in comment.