Answer: Text Formulas are used to manipulate, combine, and extract data from text strings in Excel.
Text formulas or functions in Excel are designed to handle text data, enabling users to perform various operations on text strings such as concatenating, extracting specific characters, replacing parts of a text string, changing text case, and more.
These formulas are particularly useful when dealing with large datasets that contain a mix of text and numeric data, or when one needs to format and clean the data for further analysis.
Overview of Text Formulas in Excel
Here’s an overview of some common text formulas in Excel:
- CONCATENATE / CONCAT: Combine text strings from multiple cells into one cell.
- LEFT, RIGHT, MID: Extract specific characters from a text string.
- LEN: Return the length of a text string.
- FIND, SEARCH: Find the position of a specific character or substring within a text string.
- REPLACE, SUBSTITUTE: Replace parts of a text string with other text.
- UPPER, LOWER, PROPER: Change the case of text strings.
- TRIM: Remove extra spaces from a text string.
- VALUE: Convert a text string to a number.
- TEXT: Format a number as a text string.
Concatenation
CONCATENATE or CONCAT functions are used to join text strings from multiple cells into one cell. The syntax for CONCATENATE is:
=CONCATENATE(text1, [text2], ...)
The syntax for CONCAT is:
=CONCAT(text1, [text2], ...)
Example:
=CONCATENATE("Hello", " ", "World") or =CONCAT("Hello", " ", "World")
This formula will return “Hello World”.
Extracting Text
LEFT
The LEFT function extracts a specific number of characters from the beginning of a text string. The syntax is:
=LEFT(text, [num_chars])
Example:
=LEFT("EduPepper", 3)
This formula will return “Edu”.
RIGHT
The RIGHT function extracts a specific number of characters from the end of a text string. The syntax is:
=RIGHT(text, [num_chars])
Example:
=RIGHT("EduPepper", 3)
This formula will return “per”.
MID
The MID function extracts a specific number of characters from a text string, starting at a specified position. The syntax is:
=MID(text, start_num, num_chars)
Example:
=MID("EduPepper", 2, 3)
This formula will return “duP”.
Length of Text
The LEN function returns the number of characters in a text string. The syntax is:
=LEN(text)
Example:
=LEN("EduPepper")
This formula will return 9.
Finding Text Position
FIND
The FIND function returns the position of a specific character or substring within a text string. It is case-sensitive. The syntax is:
=FIND(find_text, within_text, [start_num])
Example:
=FIND("Pepper", "EduPepper")
This formula will return 4.
SEARCH
The SEARCH function is similar to the FIND function, but it is not case-sensitive. The syntax is:
=SEARCH(find_text, within_text, [start_num])
Example:
=SEARCH("pepper", "EduPepper")
This formula will return 4.
Replacing and Substituting Text
REPLACE
The REPLACE function replaces part of a text string with another text string, based on the starting position and length of the characters being replaced. The syntax is:
=REPLACE(old_text, start_num, num_chars, new_text)
Example:
=REPLACE("EduPepper", 4, 4, "Blog")
This formula will return “EduBlog”.
SUBSTITUTE
The SUBSTITUTE function replaces specific occurrences of a text string with another text string. The syntax is:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Example:
=SUBSTITUTE("Hello World World", "World", "Excel", 2)
This formula will return “Hello World Excel”.
Changing Text Case
UPPER
The UPPER function converts a text string to uppercase. The syntax is:
=UPPER(text)
Example:
=UPPER("EduPepper")
This formula will return “EDUPEPPER”.
LOWER
The LOWER function converts a text string to lowercase. The syntax is:
=LOWER(text)
Example:
=LOWER("EduPepper")
This formula will return “edupepper”.
PROPER
The PROPER function capitalizes the first letter of each word in a text string and converts the rest of the letters to lowercase. The syntax is:
=PROPER(text)
Example:
=PROPER("hello world")
This formula will return “Hello World”.
Trimming Text
The TRIM function removes extra spaces from a text string, leaving only single spaces between words and no spaces at the beginning or end of the text. The syntax is:
=TRIM(text)
Example:
=TRIM(" Hello World ")
This formula will return “Hello World”.
Converting Text to Number
The VALUE function converts a text string that represents a number to a numeric value. The syntax is:
=VALUE(text)
Example:
=VALUE("123")
This formula will return 123.
Formatting Number as Text
The TEXT function formats a number as a text string using a specified format. The syntax is:
=TEXT(value, format_text)
Example:
=TEXT(12345.678, "#,##0.00")
This formula will return “12,345.68”.