Upper case for first letter of the word

Gaurav Chauhan

Hi Team,

Can you please confirm how to update the first letter to upper case. I have a formula that extracts the first name and last name from email id.

so I am getting "gaurav" from gaurav.chauhan#amplity.com.

I wish to change "gaurav" to "Gaurav"

Thanks, Gaurav

Jeff Reisman

If you have the first name in its own column, try this:

=UPPER(LEFT(FirstName@row, 1)) + LOWER(MID(FirstName@row, 2, 40))

This takes the first character from the left and coverts it to uppercase, then adds on the rest of the name in lowercase. This assumes the first name is not longer than about 40 characters long.

Jon Moon

I was working on the same scenario and came across Jeff's reply which works nicely, but like Gurav I wanted to extract the firstname from an contact email field so have tweaked Jeff's function slightly to replace "FirstName@row" with the function to extract the name from the email and end up with:

=UPPER(LEFT(LEFT([Contact Email]@row, FIND(".", [Contact Email]@row) - 1), 1)) + LOWER(MID(LEFT([Contact Email]@row, FIND(".", [Contact Email]@row) - 1), 2, 40))

Gaurav Chauhan

Thank you. This formulae works to pull the first name starting with Capital letter.

=UPPER(LEFT(LEFT([Contact Email]@row, FIND(".", [Contact Email]@row) - 1), 1)) + LOWER(MID(LEFT([Contact Email]@row, FIND(".", [Contact Email]@row) - 1), 2, 40))

So how do I now get the last name as well with the first letter being capital

so gaurav.chauhan@abc.com would get converted to Gaurav Chauhan

P.S. My current formulae that gets me to GAURAV CHAUHAN is

=UPPER(LEFT([Created by]@row, FIND(".", [Created by]@row) - 1)) + " " + UPPER(LEFT(RIGHT([Created by]@row, LEN([Created by]@row) - FIND(".", [Created by]@row)), FIND("@", RIGHT([Created by]@row, LEN([Created by]@row) - FIND(".", [Created by]@row))) - 1))

Gaurav Chauhan

Hi, Please help confirm.

Jeff Reisman

@Gaurav Chauhan The logic is as follows:

Use FIND to locate the position of the "." and the position of the "@" in the email address. So let's say the "." is at position 9, and the @ is at position 20.

Start with using UPPER with LEFT to get the first letter capitalized: =UPPER(LEFT([Created by]@row, 1)) . Then use LOWER with MID to get letters 2-8 by starting at 2 and getting the next 7 letters: =LOWER(MID([Created by]@row, 2, 7)). Add the two together and you get the first name starting with a capital letter, and add a space at the end. =UPPER(LEFT([Created by]@row, 1)) + LOWER(MID([Created by]@row, 2, 7)) + " "

Next we'll get the last name:

=UPPER(MID([Created by]@row, 10, 1)) + LOWER(MID([Created by]@row, 11, 9))

Add them both up:

=UPPER(LEFT([Created by]@row, 1)) + LOWER(MID([Created by]@row, 2, 7)) + " " + UPPER(MID([Created by]@row, 10, 1)) + LOWER(MID([Created by]@row, 11, 9))

You can use FIND along with some math to use formulas in place of the values in bold above. The 7 comes from the position of the "." minus 2. The 10 comes from adding 1 to the position of the ".". The 11 comes from adding 2 to the position of the ".". And the 9 comes from subtracting the the position of the "." from the position of the "@".

The 1, 2, and 1 in italics are hardcoded numbers that shouldn't change.

Just a note: I normally won't get notifications of replies on older posts unless you use the @ functionality to mention me (@Jeff Reisman ) in your reply.