Basic Text Functions (CONCATENATE, UPPER, LOWER)0%

Basic Text Functions (CONCATENATE, UPPER, LOWER)

Beginner12 min read•Updated: Jul 11, 2026

Basic Text Functions (CONCATENATE, UPPER, LOWER)

Spreadsheets frequently contain text data—names, email addresses, department codes, and product SKUs—that arrive poorly formatted or fragmented. Excel's text functions allow you to join strings, adjust letter casing, and clean messy imports.

Joining Text: The Ampersand Operator ('&') vs. CONCAT

Combining text from separate cells (e.g., merging First Name in A2 and Last Name in B2 into a Full Name):

The Ampersand ('&') Operator (The Industry Standard)

The ampersand symbol acts as a text glue operator:

1=A2 & " " & B2

If A2 is "Sarah" and B2 is "Jenkins", the formula evaluates to "Sarah Jenkins".
Notice: You must include " " (space enclosed in quotation marks) between the ampersands; otherwise, Excel will produce "SarahJenkins"!

The CONCAT & TEXTJOIN Functions

CONCAT: Replaced legacy CONCATENATE in modern Excel:

1  =CONCAT(A2, " ", B2)

TEXTJOIN: The modern powerhouse function! Joins an entire range of cells using a designated delimiter and automatically ignores blank cells:

1  =TEXTJOIN(", ", TRUE, A2:A10)

Produces a clean, comma-separated list in a single cell!

Changing Text Casing

Unlike Microsoft Word, Excel does not have a "Change Case" button on the Ribbon. You must use functions:

UPPER: Converts all letters to UPPERCASE.

1  =UPPER("california")  ➔  "CALIFORNIA"

LOWER: Converts all letters to lowercase (perfect for generating standard email addresses).

1  =LOWER("John.Smith@COMPANY.com")  ➔  "john.smith@company.com"

PROPER: Capitalizes the first letter of every word while making all other letters lowercase (Title Case).

1  =PROPER("mIChAel cHeN")  ➔  "Michael Chen"

Removing Unwanted Spaces: The TRIM Function

Imported database data frequently contains invisible trailing or leading spaces that break lookup formulas:

1=TRIM(A2)

Removes all leading and trailing spaces, and reduces multiple consecutive spaces between words down to a single space.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which formula correctly merges the First Name in cell A2 and the Last Name in cell B2 with a space between them?

Question 2

Which Excel text function converts messy mixed-case names (such as "jOhN dOE") into properly capitalized title case ("John Doe")?

Question 3

What does the TRIM function do in Microsoft Excel?

Question 4

Which modern function can merge a range of 20 cells into a single comma-separated list while automatically skipping empty cells?

Question 5

What does the formula =LOWER("EXCEL 2026") return?

Next Lesson

AutoFill and Relative Cell References

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SUM, AVERAGE, MIN, MAX Functions	AutoFill and Relative Cell References

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

AutoFill and Relative Cell References

Basic Text Functions (CONCATENATE, UPPER, LOWER)

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Basic Text Functions (CONCATENATE, UPPER, LOWER)

Joining Text: The Ampersand Operator ('&') vs. CONCAT

Combining text from separate cells (e.g., merging First Name in A2 and Last Name in B2 into a Full Name):

The Ampersand ('&') Operator (The Industry Standard)

The ampersand symbol acts as a text glue operator:

1=A2 & " " & B2

If A2 is "Sarah" and B2 is "Jenkins", the formula evaluates to "Sarah Jenkins".
Notice: You must include " " (space enclosed in quotation marks) between the ampersands; otherwise, Excel will produce "SarahJenkins"!

The CONCAT & TEXTJOIN Functions

CONCAT: Replaced legacy CONCATENATE in modern Excel:

1  =CONCAT(A2, " ", B2)

TEXTJOIN: The modern powerhouse function! Joins an entire range of cells using a designated delimiter and automatically ignores blank cells:

1  =TEXTJOIN(", ", TRUE, A2:A10)

Produces a clean, comma-separated list in a single cell!

Changing Text Casing

Unlike Microsoft Word, Excel does not have a "Change Case" button on the Ribbon. You must use functions:

UPPER: Converts all letters to UPPERCASE.

1  =UPPER("california")  ➔  "CALIFORNIA"

LOWER: Converts all letters to lowercase (perfect for generating standard email addresses).

1  =LOWER("John.Smith@COMPANY.com")  ➔  "john.smith@company.com"

PROPER: Capitalizes the first letter of every word while making all other letters lowercase (Title Case).

1  =PROPER("mIChAel cHeN")  ➔  "Michael Chen"

Removing Unwanted Spaces: The TRIM Function

Imported database data frequently contains invisible trailing or leading spaces that break lookup formulas:

1=TRIM(A2)

Removes all leading and trailing spaces, and reduces multiple consecutive spaces between words down to a single space.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which formula correctly merges the First Name in cell A2 and the Last Name in cell B2 with a space between them?

Question 2

Which Excel text function converts messy mixed-case names (such as "jOhN dOE") into properly capitalized title case ("John Doe")?

Question 3

What does the TRIM function do in Microsoft Excel?

Question 4

Which modern function can merge a range of 20 cells into a single comma-separated list while automatically skipping empty cells?

Question 5

What does the formula =LOWER("EXCEL 2026") return?

Next Lesson

AutoFill and Relative Cell References

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SUM, AVERAGE, MIN, MAX Functions	AutoFill and Relative Cell References

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next