Text Functions in Excel: LEFT, RIGHT, MID, CONCAT, and TEXTJOIN

What Are Text Functions in Excel?

Text functions in Excel help you manipulate, extract, and combine text data. They’re perfect for cleaning up imported data, reformatting information, or even building custom labels.

We’ll explore five of the most useful ones today:

• LEFT – Extracts text from the start (left side)
• RIGHT – Extracts text from the end (right side)
• MID – Extracts text from the middle
• CONCAT – Joins multiple pieces of text together
• TEXTJOIN – Joins text with delimiters like commas or spaces

1. The LEFT Function

Purpose:

The LEFT function extracts a specific number of characters from the beginning of a text string.

Syntax:

=LEFT(text, [num_chars])

• text – The cell or string you want to extract from.
• num_chars – The number of characters you want to extract (optional, defaults to 1).

Example:

If cell A1 contains iTechTuts, and you want the first 5 letters:

=LEFT(A1,5)

Result:iTech

Real-world use case: Extract the year from a date code like 2025-EXCEL using =LEFT(A1,4).

2. The RIGHT Function

Purpose:

The RIGHT function extracts characters from the end (right side) of a string.

Syntax:

=RIGHT(text, [num_chars])

• text – The cell containing the text.
• num_chars – The number of characters from the end you want.

Example:

If A1 contains iTechTuts, and you want the last 4 letters:

=RIGHT(A1,4)

Result:Tuts

Real-world use case: Extracting the last digits from a product ID like ABC-4578 using =RIGHT(A1,4).

3. The MID Function

Purpose:

The MID function extracts text from the middle of a string — perfect when the info you need isn’t at the start or end.

Syntax:

=MID(text, start_num, num_chars)

• text – The text you want to extract from.
• start_num – The position to start extracting from.
• num_chars – How many characters to extract.

Example:

If A1 contains iTechTuts, and you want “Tech”, start at position 2 and extract 4 characters:

=MID(A1,2,4)

Result:Tech

Real-world use case: Extracting codes embedded in long strings like INV2025X123 → =MID(A1,4,4) gives 2025.

4. The CONCAT Function

Purpose:

The CONCAT function (short for concatenate) combines text from multiple cells or strings into one continuous string.

Syntax:

=CONCAT(text1, [text2], ...)

• text1, text2... – The cells or text strings you want to combine.

Example:

If A1 = John and B1 = Doe:

=CONCAT(A1, " ", B1)

Result:John Doe

Pro Tip: CONCAT replaces the older CONCATENATE function and works faster, especially in modern Excel versions.

5. The TEXTJOIN Function

Purpose:

The TEXTJOIN function takes text combining to the next level. It lets you join multiple text strings using a delimiter (like commas or spaces) and even skip blank cells automatically.

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter – The separator between each text (e.g., space, comma).
• ignore_empty – TRUE or FALSE (set TRUE to ignore empty cells).
• text1, text2... – The cells or ranges to join.

Example:

If A1=John, B1=Doe, and C1=Smith:

=TEXTJOIN(" ", TRUE, A1:C1)

Result:John Doe Smith

Pro Tip: TEXTJOIN is the modern, smarter cousin of CONCAT — perfect for joining long text lists efficiently.

Practical Example: Cleaning and Formatting Names

Let’s say you have the following data:

A1: First Name | B1: Last Name
A2: Priya       | B2: Sharma
A3: Arjun       | B3: Mehta
A4: Neha        | B4: Gupta
    

To combine full names with a space:

=TEXTJOIN(" ", TRUE, A2, B2)

Result:Priya Sharma

Want only the initials? Try:

=LEFT(A2,1) & LEFT(B2,1)

Result:PS

Simple yet powerful!

Common Mistakes to Avoid

• ❌ Forgetting to use quotation marks for text or delimiters.
• ❌ Using the wrong number of characters in LEFT, RIGHT, or MID.
• ❌ Confusing CONCAT with TEXTJOIN — remember, TEXTJOIN supports delimiters and ranges!
• ✅ Always test your formula on one or two cells first before applying it to a full column.

Conclusion

And that’s a wrap! 🎉 You’ve just learned how to control text like a pro in Excel using LEFT, RIGHT, MID, CONCAT, and TEXTJOIN. These functions are lifesavers when cleaning data, merging names, or organizing messy text entries.

Next step? Open your Excel workbook and try them out. The more you practice, the more magical Excel feels — and soon, you’ll be teaching others how to do it!