Lesson 3.5 – Text Functions (LEFT, RIGHT, MID, LEN, CONCAT)

Text functions help you extract, combine, and analyze text values in Excel. They are essential for cleaning data, preparing reports, and working with names, codes, product IDs, and imported datasets. In questa lezione impari cinque funzioni fondamentali: LEFT, RIGHT, MID, LEN e CONCAT.

---

1. LEFT – Extract Characters from the Left

Syntax:

=LEFT(text, number_of_characters)

Example:

=LEFT("ProductA", 7)

Result: Product

LEFT is useful for extracting prefixes, codes, or the first part of a string.

---

2. RIGHT – Extract Characters from the Right

Syntax:

=RIGHT(text, number_of_characters)

Example:

=RIGHT("AB12345", 5)

Result: 12345

RIGHT is ideal for extracting numeric codes, suffixes, or final characters.

---

3. MID – Extract Characters from the Middle

Syntax:

=MID(text, start_position, number_of_characters)

Example:

=MID("AB-2024-IT", 4, 4)

Result: 2024

MID is perfect for extracting values inside structured codes or formatted text.

---

4. LEN – Count Characters

Syntax:

=LEN(text)

Example:

=LEN("Excel")

Result: 5

LEN helps you check text length, validate codes, or find inconsistencies.

---

5. CONCAT – Combine Text

Syntax:

=CONCAT(text1, text2, ...)

Example:

=CONCAT(A2, " ", B2)

Result: John Smith

CONCAT joins text from multiple cells. It replaces the older CONCATENATE function.

---

6. Practical Example

Suppose you have a list of full names in column A. You can:

• Extract the first 3 letters → =LEFT(A2, 3)
• Extract the last 4 letters → =RIGHT(A2, 4)
• Extract the middle part → =MID(A2, 2, 3)
• Count characters → =LEN(A2)
• Combine name + code → =CONCAT(A2, "-", B2)

---

7. Common Mistakes to Avoid

• Using text functions on numbers without converting them
• Forgetting that LEN counts spaces
• Using CONCAT instead of TEXTJOIN when separators are needed
• Extracting too many or too few characters

---

8. Practical Exercise

Practice text functions with the following steps:

1. Create a worksheet named Lesson_3_5_Practice.
2. Enter 10 text values (names, codes, product IDs).
3. Use LEFT to extract the first 3 characters.
4. Use RIGHT to extract the last 2 characters.
5. Use MID to extract the central part.
6. Use LEN to count characters.
7. Use CONCAT to combine two columns.

---

Internal Links

Continua a imparare con le lezioni precedenti e successive:

• Lesson 3.3 – Math Functions (SUM, AVERAGE, MIN, MAX)
• Lesson 3.4 – Counting Functions (COUNT, COUNTA, COUNTBLANK)
• Lesson 3.6 – Logical Functions (IF, AND, OR)

---

Next Lesson

Lesson 3.6 – Logical Functions (IF, AND, OR)