Manipulating text is a common task in spreadsheets, whether you're preparing data for reports, formatting it for presentation, or extracting specific information for analysis. The following text manipulation formulas are indispensable tools for these purposes.

CONCATENATE / CONCAT: Combining Text from Different Cells

The CONCATENATE (or CONCAT in newer versions) function joins two or more text strings into one string. This is particularly useful for merging first and last names, combining addresses, or creating unique identifiers.

Example Data:

   A       B
1  John    Doe
2  Jane    Smith

Formula (CONCAT): =CONCAT(A1, " ", B1)

Result: John Doe

This formula combines the first name in cell A1 with the last name in cell B1, separated by a space.

LEFT, RIGHT, MID: Extracting Specific Text from a Cell

These functions are used to extract specific portions of a text string. LEFT returns the first characters from the start of the string, RIGHT returns the last characters, and MID extracts a substring from the middle.

Example Data:

   C
1  123-456-7890

LEFT Formula: =LEFT(C1, 3)

Result: 123

RIGHT Formula: =RIGHT(C1, 4)

Result: 7890

MID Formula: =MID(C1, 5, 3)

Result: 456

These formulas extract the area code, the last four digits, and the central three digits of a phone number, respectively.

UPPER, LOWER, PROPER: Changing the Text Case

These functions adjust the case of the text strings in your data. UPPER converts text to all uppercase, LOWER to all lowercase, and PROPER to title case (the first letter of each word is capitalized).

Example Data:

   D
1  spreadsheet tools

UPPER Formula: =UPPER(D1)

Result: SPREADSHEET TOOLS

LOWER Formula: =LOWER("EXCEL FUNCTIONS")

Result: excel functions

PROPER Formula: =PROPER(D1)

Result: Spreadsheet Tools

These functions are incredibly useful for standardizing the text data in your spreadsheets, ensuring consistency across your dataset, especially when preparing data for reports or presentations.

By mastering these text manipulation formulas, you can significantly enhance your ability to handle and format text data in your spreadsheets, making your data cleaner, more consistent, and easier to analyze.

Text Manipulation Formulas

• CONCATENATE / CONCAT: Combining text from different cells.
• LEFT, RIGHT, MID: Extracting specific text from a cell.
• UPPER, LOWER, PROPER: Changing the text case.