Why Are Excel Text Functions Important?

In today's data-driven world, raw data rarely comes in a perfectly formatted state. You might have:

• Full names that need to be split into first and last names.
• Product codes with specific segments that need to be extracted.
• Addresses that need to be broken down into street, city, and zip code.
• Data from different sources that needs to be combined into a single, meaningful string.

Text functions allow you to automate these tasks, ensuring consistency and accuracy. They are the building blocks for more complex data cleaning and transformation processes.

1. The LEFT Function: Extracting Characters from the Beginning

The LEFT function is your go-to for pulling a specified number of characters from the beginning (the left side) of a text string.

Syntax:

LEFT(text, num_chars)

• text: This is the text string from which you want to extract characters. It can be a cell reference or a direct text string.
• num_chars: This is the number of characters you want to extract, starting from the leftmost character.

Practical Example: Extracting First Names

Imagine you have a list of full names in Column A, and you want to extract just the first names into Column B.

Let's say cell A2 contains "Alice Wonderland".

To extract the first name, you need to find the position of the first space, as everything before it is the first name. However, LEFT is simpler if you know the maximum possible length or if the first name is consistently short. For this example, let's assume first names are generally around 5 characters or less, or you want to extract the first 5 characters for demonstration.

Formula in B2:

=LEFT(A2, 5)

This formula will return "Alice".

A More Robust Approach (using FIND):

A more common scenario is extracting the first name up to the first space. This requires combining LEFT with the FIND function.

Formula in B2 (if A2 is "Alice Wonderland"):

=LEFT(A2, FIND(" ", A2) - 1)

Here:

• FIND(" ", A2) finds the position of the first space in cell A2 (which is position 6).
• Subtracting 1 (- 1) gives us the number of characters before the space (5).
• LEFT(A2, 5) then extracts those 5 characters.

This robust formula will correctly extract "Alice" from "Alice Wonderland" and "Bob" from "Bob The Builder".

Compatibility Note: The LEFT function works identically in Microsoft Excel, LibreOffice Calc, and Google Sheets.

2. The RIGHT Function: Extracting Characters from the End

Just as LEFT extracts from the beginning, RIGHT extracts characters from the end (the right side) of a text string.

Syntax:

RIGHT(text, num_chars)

• text: The text string from which to extract.
• num_chars: The number of characters to extract from the right.

Practical Example: Extracting Last 4 Digits of a Code

Suppose you have product codes in Column A, and you need to extract the last 4 digits, which represent a specific product version.

Let's say cell A2 contains "PROD-XYZ-1234".

Formula in B2:

=RIGHT(A2, 4)

This formula will return "1234".

A More Robust Approach (using LEN and FIND):

If you wanted to extract the last name, you'd need to find the last space. A common way to extract the "last part" is by using the total length of the string and finding the position of a delimiter from the right. However, for simpler cases like extracting a fixed number of characters from the end, RIGHT is perfect.

Compatibility Note: RIGHT is also consistent across Excel, LibreOffice Calc, and Google Sheets.

3. The MID Function: Extracting Characters from the Middle

The MID function is the most versatile of the extraction functions. It allows you to extract a specified number of characters from a text string, starting at any position you choose.

Syntax:

MID(text, start_num, num_chars)

• text: The text string from which to extract.
• start_num: The position of the first character you want to extract. The first character in the string is position 1.
• num_chars: The number of characters you want to extract.

Practical Example: Extracting Middle Part of an ID

Consider an employee ID format like "EMP-ABC-123" in cell A2, where you want to extract the "ABC" part.

In "EMP-ABC-123":

• 'E' is at position 1
• 'M' is at position 2
• 'P' is at position 3
• '-' is at position 4
• 'A' is at position 5 (this is our desired start position)
• 'B' is at position 6
• 'C' is at position 7

We want to extract 3 characters starting from position 5.

Formula in B2:

=MID(A2, 5, 3)

This formula will return "ABC".

A More Robust Approach (using FIND):

If the ID format varies, you might need to use FIND to locate the delimiters.

For an ID like "EMP-ABC-123" where you want "ABC":

Formula in B2:

=MID(A2, FIND("-", A2) + 1, FIND("-", A2, FIND("-", A2) + 1) - FIND("-", A2) - 1)

                

                    
Generate Excel Formulas with AI
                    
Need help creating formulas? Use ExcelFormula Pro to generate them instantly with AI!
                    Try Free