Applying Text Manipulation Formulas (CONCATENATE, LEFT, RIGHT)

Text manipulation formulas in Excel are invaluable for combining, extracting, and manipulating text strings to meet specific requirements. Three commonly used text manipulation formulas are CONCATENATE, LEFT, and RIGHT. Let’s explore how to apply these formulas effectively.

Table of Contents

CONCATENATE

The CONCATENATE function allows you to combine multiple text strings into a single string. It is particularly useful when you need to merge different pieces of text together.

=CONCATENATE(text1, text2, …)

text1, text2, …: The text strings you want to concatenate. You can input up to 255 text strings as arguments.

Suppose you have first name in cell A1 and last name in cell B1. To concatenate them into a single full name in cell C1, you would use the formula:

=CONCATENATE(A1, " ", B1)

LEFT Function

The LEFT function allows you to extract a specified number of characters from the beginning of a text string. It comes in handy when you want to retrieve a portion of a text string.

=LEFT(text, num_chars)

text: The text string from which you want to extract characters.
num_chars: The number of characters you want to extract from the left side of the text string.

You have a product code in cell A1 that consists of a four-digit number. To extract the first two digits, you would use the formula:

=LEFT(A1, 2)

RIGHT Function

The RIGHT function allows you to extract a specified number of characters from the end of a text string. It is useful when you want to retrieve a portion of a text string.

=RIGHT(text, num_chars)

text: The text string from which you want to extract characters.
num_chars: The number of characters you want to extract from the right side of the text string.

Yyou have a phone number in cell A1 that ends with the last four digits representing an extension. To extract the extension, you would use the formula:

=RIGHT(A1, 4)

Effectively applying these text formulas, allow you to merge, extract, and modify text strings to suit your specific needs. CONCATENATE helps you combine text, LEFT allows you to extract characters from the left side, and RIGHT enables you to extract characters from the right side of a text string. These formulas provide great flexibility in handling text-related tasks and customizing text outputs in Excel.