3.4. Text Functions: LEFT, RIGHT, MID, CONCATENATE, TEXT

Text functions in Excel are powerful tools that allow you to manipulate and extract information from text strings. These functions enable you to clean, combine, and format text in various ways, making it easier to analyze and present data. In this section, we will explore some of the most commonly used text functions in Excel: LEFT, RIGHT, MID, CONCATENATE, and TEXT.

1. LEFT Function

The LEFT function extracts a specified number of characters from the beginning (left side) of a text string. This is useful when you need to extract the first few characters of a string, such as getting the area code from a phone number or the first name from a full name.

Syntax:

=LEFT(text, num_chars)

• text: The text string or cell reference containing the text from which you want to extract characters.
• num_chars: The number of characters you want to extract from the left side of the text.

Example:

=LEFT("Hello World", 5)

This formula will return "Hello", as it extracts the first 5 characters from the text string "Hello World".

2. RIGHT Function

The RIGHT function works similarly to the LEFT function, but it extracts characters from the right side of a text string. This is useful when you need to extract parts of data from the end of a string, such as extracting the year from a date or a product code from an SKU.

Syntax:

=RIGHT(text, num_chars)

• text: The text string or cell reference containing the text.
• num_chars: The number of characters you want to extract from the right side of the text.

Example:

=RIGHT("Hello World", 5)

This formula will return "World", as it extracts the last 5 characters from the text string "Hello World".

3. MID Function

The MID function extracts characters from the middle of a text string, starting at a specified position and extracting a given number of characters. This function is useful when you need to extract a specific portion of a string that is not at the beginning or end.

Syntax:

=MID(text, start_num, num_chars)

• text: The text string or cell reference containing the text.
• start_num: The position of the first character you want to extract (starting from 1).
• num_chars: The number of characters you want to extract.

Example:

=MID("Hello World", 7, 5)

This formula will return "World", as it starts at position 7 and extracts 5 characters from the text string "Hello World".

4. CONCATENATE Function

The CONCATENATE function allows you to join two or more text strings into a single string. While CONCATENATE is commonly used to merge text from different cells (like combining first and last names), Excel's newer versions recommend using the CONCAT function for this purpose. However, CONCATENATE remains widely used and can be helpful for older spreadsheets.

Syntax:

=CONCATENATE(text1, text2, ...)

• text1, text2, ...: The text strings or cell references to be combined.

Example:

=CONCATENATE("Hello", " ", "World")

This formula will return "Hello World", as it joins the text strings "Hello", a space " ", and "World".

Note: Starting in Excel 2016, CONCATENATE is being replaced with the CONCAT function, which works in the same way.

5. TEXT Function

The TEXT function is used to convert a numeric value into text and apply formatting to it. This is especially useful when you want to display dates, times, numbers, or currency in a custom format while still keeping them as text for further manipulation or presentation.

Syntax:

=TEXT(value, format_text)

• value: The numeric value or date that you want to convert to text.
• format_text: The format you want to apply to the value, enclosed in quotation marks.

Common Format Codes:

• "0": A number with no decimal places (e.g., TEXT(1234.56, "0") results in "1235").
• "0.00": A number with two decimal places (e.g., TEXT(1234.56, "0.00") results in "1234.56").
• "dd/mm/yyyy": A date in day/month/year format.
• "$0.00": A currency format.

Example:

=TEXT(1234.56, "$0.00")

This formula will return "$1234.56", converting the numeric value into a text string formatted as currency.

6. Combining Text Functions for More Advanced Operations

You can combine these text functions to perform more complex operations. For example, if you have a full name and you want to extract the first name and last name into separate columns, you can use the LEFT, RIGHT, and MID functions together with SEARCH or FIND.

Example:

=LEFT(A1, SEARCH(" ", A1)-1)

This formula extracts the first name from the full name in cell A1 by searching for the first space character.

7. Practical Use Cases for Text Functions

• Extracting Date Components: Use MID, LEFT, and RIGHT to extract parts of dates, such as the day, month, or year.
• Phone Numbers: Use LEFT, MID, or RIGHT to format and extract specific parts of a phone number.
• Email Parsing: Extract the domain or username from an email address using MID, LEFT, and RIGHT functions.
• Address Parsing: Extract components of an address (street, city, zip code) into separate cells using these functions.

8. Conclusion

Text functions in Excel are essential tools for anyone working with large datasets or text-based information. They allow you to clean, format, and manipulate text strings efficiently, saving time and ensuring your data is properly formatted for analysis or presentation. By mastering functions like LEFT, RIGHT, MID, CONCATENATE, and TEXT, you can transform complex text-based data into useful insights and reports.