Upper case for first letter of the word
![Gaurav Chauhan](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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))
-
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))
-
Hi, Please help confirm.
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!