Let us look at how to change cases of text in excel. Changing the entire text range from lower to higher and vice versa is straight forward. However, if you want to change the case for a particular character (like only first character of a text string) needs a bit of logic in the formula. Let’s look at both the cases.
First the straight forward way of changing cases to the entire text string.
Formula to change the lower case to upper case will be “=upper(text)“
Formula to change the upper case text to lower case will be “=lower(text)“
Next, let’s look at changing the case of the first letter in text string. To achieve this, we need to know few other formulas – Left, Right, Len. Let’s quickly run through these formulas.
Left: This formula returns the left side characters of the text string. The formula needs to specify the number characters – how many left side characters should be returned.
Right: Same as left – but returns the right side characters of a text string, based on the number characters.
Len: Returns the total length of characters in the text field.
For the text “london bridge is falling down”, =left(text,6) would return “london”. =right(text,4) would return “down”. =len(text) would return 29.
The logic to change the case of the first letter in the text string: to return the first character of left side character, convert that into upper and then concatenate the rest of characters.
To return the first character and to convert to upper case, use the below formula:
Then let’s return all the other characters using the right formula and len. Formula to be used:
We are subtracting one from the entire length as we have already returned “L”
Now, let’s join both the formulas
One can play with the length and left/right numbers for getting upper / lower case in a text string.
I hope you liked this tutorial and found helpful. Please help us with your valuable feedback which would help us to improve the content. Thanks for reading.