Excel TEXT & Rounding Functions
Contents
Concatenation
Text
Right, Left, Len & Find
Substitute
Proper, Lower & Upper
Round
Currency
Feet & Inches
MS Excel with US
s
About functions
Most of the functions you’ve learned so far focus on crunching raw numbers. But Excel also provides
functions that work with other types of data, including dates, times, and ordinary text. For text, for
example, you may want Excel to pull first and last names from two different columns and join them
in a single column.
Or you may want to find and remove a word that appears in a bunch of column titles. Similarly, you
may want to replace a character in a word, capitalize a name, or count the number of letters in a
cell. Excel provides specialized text functions for all these tasks,
0
But Excel also provides
dinary text. For text, for
columns and join them
mn titles. Similarly, you
number of letters in a
Concatenation
Description (Result) Formula Operator
Join several text strings into one text
=CONCATENATE("text1","text2") ="text1" & "text2"
string
Live Results
Employee Sales
Rizwan $25,000.00
Your text should look like this:
Sales by Mr. Rizwan is 25000 Dollar
concatenate function
operator "&"
First Name Second Name Full Name
EDITH ABBOTT
GRACE ABBOTT
VITTORIA ACCORAMBONI
ABIGAIL SMITH ADAMS
ANNETTE ADAMS
HANNAH ADAMS
JANE ADDAMS
MAYA ANGELOU
KATHARINE SUSAN ANTHONY
SUSAN BROWNELL ANTHONY
CORAZON AQUINO
MARIE ANTOINETTE
Excel's Text Functions
Formula
=Text(value, format_text)
Original Required Formula
23.5 23.50 =TEXT(B8,"0.00")
0.325 32.5% =TEXT(B9,"%.0")
Salesperson Sales
Burke 2800
Dykstra 40%
Formula Description Result
=B14&" sold Combines cell A2, the text Burke sold $2800.00 worth
"&TEXT(C14, "$0.00")&" string " sold," cell B2 of units.
worth of units." (formatted as currency),
and the text string " worth
of units." into a phrase.
=B15&" sold Combines cell A3, the Dykstra accounted for 40%
"&TEXT(C15,"0%")&" of string " sold," cell B3 of the total sales.
the total sales." (formatted as a
percentage), and the text
string " of the total sales."
into a phrase.
Ratio Company's Ratio Industry's Avg Ratio
Current Ratio 1.55 1.50
Gearing Ratio 60% 30%
Your text should look like this:
Current Ratio of company is 1.65 that is not much higher than industry avg ratio of 1.5
Gearing Ratio of company is 50% that is significantly higher than industry avg ratio of 30%
1
2
Note: Text function with date will be covered in date function
Output
TRY it OUT
try avg ratio of 1.5
ustry avg ratio of 30%
Excel's Right, Left, Len & Find
Function
Data
Chichawatani
Formula Desciption
It returns specified number of
=RIGHT(B5,4)
characters from right of the string
It returns specified number of
=LEFT(B5,4)
characters from left of the string
=LEN(B5) It returns the length of the string
It returns the position of the letter in
=FIND("r",B5,1)
the string
It returns specified number of
=MID(B5,3,2)
characters from mid of the string
Date Day
20141122
20140517
20140405
20141211
Split full name into first name and second name
Full Name First Name
EDITH ABBOTT
GRACE GOLD
VITTORIA ACCORAMBONI
ABIGAIL SMITH
ANNETTE ADAMS
HANNAH ADAMS
JANE MAX
MAYA ANGELOU
KATHARINE ANTHONY
SUSAN BROWNELL
CORAZON AQUINO
MARIE ANTOINETTE
Result Try it Out
achi
Kara
ra
Month Year
Second Name
Substitute Function
Data
Original Text
Replace Rizwan with:
Replace Rizwan with:
Ali
Mohsin
Kashif
Mr. Rizwan progress report is attached
Formula
Porper, Lower & Upper
Data
KaRaChI RoCkS
Formula Desciption Result
=PROPER(B5) converts text to initial-case Karachi Rocks
=LOWER(B5) converts text to all lower case karachi rocks
=UPPER(B5) converts text to all capital KARACHI ROCKS
Original Value LOWER() PROPER()
edith ABBOTT
grace ABBOTT
vittoria ACCORAMBONI
abigail smith ADAMS
annette ADAMS
hannah ADAMS
jane ADDAMS
maya ANGELOU
katharine ANTHONY
susan BRoWnell
corazon AQUINO
marie ANTOINETTE
Try it Out
UPPER()
Excel's Round Functions
Data
12,538.736
Formula Desciption Result
Rounds a number to specified
=ROUND(B5,2)
number of digits 12,536.76
Rounds a number down towards
=ROUNDDOWN(B5,2) zero to a specified number of
digits 12,536.75
Rounds a number up away from
=ROUNDUP(B5,2) zero to a specified number of
digits 12,536.76
Returns a number rounded to a
=MROUND(B5,5)
desired multiple 12,535.00
Amount 25,436,117.8473
Round to Round Rounddown
2 nearest cent
0 nearest dollar
-1 Nearest 10
-3 Nearest 1,000
-6 Nearest 1,000,000
Try it Out
Roundup
Round to Multiple of 5 & 15
Input Multiple of 5 Multiple of 15
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
Currency Notes & Denomination
Amount 78,421
Note Quantity
5000
1000
500
100
50
20
10
5
1
If 2.06 feet represents 2 feet 6 inches
then convert following inches into feet
Inches Feet
26
12
14
18
47
36
40
