Ask Quincy

Changing case is easily accomplished using Excel’s text functions.

UPPER	convert to upper case	=UPPER(“Kanye”) returns KANYE
LOWER	convert to lowercase	=LOWER(“KIM”) returns kim
PROPER	convert 1st  letter of each word to uppercase	=PROPER(“north west”) returns North West

Convert Text to Uppercase

1. In a blank cell enter =UPPER( text ), where text refers to the cell containing text to reformat. A copy is created in uppercase.
2. Select the newly created copy and press CTRL+C (Copy).
3. Select original cell.
4. RIGHT+CLICK and select PASTE VALUES.

For 45 second video, click here:

Cheers!

hɔuᴉnb

Additional reading:

• Excel Text Functions 1 of 4: =LEFT() and =RIGHT()
• Excel Data Consolidation
• More Excel Tips

For 60 second video, click here:

During the next series of posts I will demonstrate some commonly used Text functions. In the illustrated example we populate a CustomerCode column by combining

• the first letter of the first name,
• the first 4 letters of the last name, and
• the last 4 digits of the phone number

=LEFT

Returns the leftmost character(s) of a cell, as specified by the optional Num_chars argument.

 

Using the LEFT function to copy the leftmost characters of a cell

1. Select cell to display the result.
2. On the Formulas tab, in the Function Library group, click Text drop-down and select LEFT.
3. In Text field select or type cell containing text.
4. In the Num_chars field enter the number of characters to copy. When left blank the result is only the leftmost character.
5. Click OK.

Concatenate using ‘&’

Use the Ampersand (&) to concatenate (combine) text. For example, with “North” in A1 and “West” in B1, the formula =A1&B1 returns “NorthWest”

=RIGHT

Returns the rightmost character(s) of a cell, as specified by the optional Num_chars argument.

Using the RIGHT function to copy rightmost character(s) of a cell

1. Select cell to display the result.
2. On the Formulas tab, in the Function Library group, click Text drop-down and select RIGHT.
3. In Text field select or type cell containing text.
4. In the Num_chars field enter number of characters to copy. When left blank the result is only the rightmost character.
5. Click OK.

Cheers!

hɔuᴉnb

Additional reading:

• Insert Tables from Web into Excel
• Excel Data Consolidation
• More Excel Tips

Comments and questions are always welcome!