How to Use Variations of TEXT Functions in Excel
The basic TEXT function can help you format data into more interesting, comprehensible terms. Let’s say you have a list in an Excel spreadsheets with two headings. The first one is Sales Staff and the second is Amount Sold. In the cells below each heading, you have the names of each sales staff member along with the dollar amount he or she sold for the pertinent period.
TEXT Function
Rather than looking at the columns and rows, though, you want to spell out the information more precisely. The syntax of the TEXT function is TEXT(value,format_text). So for this example, we would select a cell in which to present our formatted text and enter the following formula
=B4&" sold “&TEXT(C4, “$0.00”)&” in the final quarter."
After typing in the formula, press Enter and the result will be “Smith sold $20000.00 in the final quarter.” Select the cell with the formula, and use the fill handle to copy it down the remaining cells.
UPPER, LOWER and PROPER
UPPER and LOWER are two Excel text function variations that work in the same way yet have the opposite effect. Using these functions, you can quickly change all of the text in the selected cells to uppercase or lowercase.
Start by selecting the cell in which you want the text to appear. Let’s say we want to make note of the sales staff member with the highest amount of sales in the final quarter. We want to advertise this fact in uppercase lettering. Start by selecting a cell in which you want this text presented and go to the Formula Bar. The syntax for the UPPER function is UPPER(text). For our example, the formula would be =UPPER(E11). Exchanging LOWER for UPPER in the formula will present the text completely in lowercase letters.
A similar variation of these Excel text functions is PROPER, which has the same syntax as UPPER and LOWER. PROPER will present the text in proper case, which is much like sentence case.
One thing you might have noted is that you will need to enter the formula into a cell other than the one containing the original text. If you try to use the selected cell in the formula, you will receive a Circular Reference error message.
