Lesson 1.9 – Text Functions (LEFT, RIGHT, MID, LEN, TRIM, PROPER, UPPER, LOWER, CONCAT, TEXTJOIN)
1. Introduction
Text Functions in Excel are used to manipulate and format text values.
They help in extracting characters, changing cases, cleaning spaces, and combining text for better presentation and analysis.
A. LEFT Function
Purpose: Extracts characters from the left side of a text string.
Syntax:
-
text → the text from which you want characters.
-
num_chars → number of characters to extract.
Example:
B. RIGHT Function
Purpose: Extracts characters from the right side of a text string.
Syntax:
Example:
C. MID Function
Purpose: Extracts characters from the middle of a text string.
Syntax:
Example:
D. LEN Function
Purpose: Counts the number of characters in a text (including spaces).
Syntax:
Example:
E. TRIM Function
Purpose: Removes extra spaces from text, leaving only single spaces between words.
Syntax:
Example:
F. PROPER Function
Purpose: Capitalizes the first letter of each word.
Syntax:
Example:
G. UPPER Function
Purpose: Converts all letters to uppercase.
Syntax:
Example:
H. LOWER Function
Purpose: Converts all letters to lowercase.
Syntax:
Example:
I. CONCAT Function
Purpose: Joins multiple text strings into one.
Syntax:
Example:
J. TEXTJOIN Function
Purpose: Joins text from multiple ranges with a delimiter (separator).
Syntax:
-
delimiter → separator like space (” “), comma (“,”), etc.
-
ignore_empty → TRUE (skip empty cells) or FALSE (include empty cells).
Example:
Key Points & Tips
-
Use TRIM before processing text to avoid hidden spaces.
-
LEN is useful for counting characters before extraction.
-
PROPER, UPPER, LOWER are helpful for text formatting.
-
TEXTJOIN is more powerful than CONCAT because it allows delimiters.
Practice Questions – Lesson 1.9
-
Extract the first 3 letters from the word “Computer”.
-
Extract the last 4 letters from the text in cell A1.
-
Extract the middle 5 characters from “Programming”.
-
Count the total number of characters in “Data Analysis”.
-
Remove extra spaces from
" Excel Skills "using a function. -
Convert
"hello world"to PROPER case. -
Combine the values in cells A1, B1, C1 using CONCAT.
-
Join values from cells A1:A5 separated by a comma using TEXTJOIN.