Working with Text in Excel: How to extract and replace words

14/07/26 •0•0

Learn effective techniques for working with text in Excel, from extracting specific words to efficiently replacing them. Improve your Excel skills and increase your productivity with these practical tips.

Geschrieben vonLeon

Übersetzt vonEditorial

Überarbeitet vonEditorial

In this lesson, we will explore Excel text functions, which are essential for manipulating, extracting, combining, and formatting text in your spreadsheets. We will cover functions such as MID, CONCATENATE, SUBSTITUTE, UPPER, LOWER, LEFT, RIGHT, LEN, among others. Each function will be explained with practical examples to make understanding easier.

Text functions in Excel

The MID function

The MID function extracts a specific part of a text, based on the starting position and the number of characters.

Syntax:

=MID(text; start_position; number_of_characters)

Example:

If you have the text "Advanced Excel" in cell A1, the formula =MID(A1; 10; 8) will return "Excel".

The CONCATENATE function

The CONCATENATE function (or CONCAT in newer versions) combines text from multiple cells into a single cell.

Syntax:

=CONCATENATE(text1; text2; ...)

Example:

If you have "John" in cell A1 and "Silva" in cell B1, the formula =CONCATENATE(A1; " "; B1) will return "John Silva".

The SUBSTITUTE function

The SUBSTITUTE function replaces part of a text with another specified text.

Syntax:

=SUBSTITUTE(text; old_text; new_text; [instance])

Example:

If you have "Excel Spreadsheet" in cell A1, the formula =SUBSTITUTE(A1; "Excel"; "Google Sheets") will return "Google Sheets Spreadsheet".

The UPPER function

The UPPER function converts all text to uppercase letters.

Syntax:

=UPPER(text)

Example:

If you have "excel" in cell A1, the formula =UPPER(A1) will return "EXCEL".

The LOWER function

The LOWER function converts all text to lowercase letters.

Syntax:

=LOWER(text)

Example:

If you have "EXCEL" in cell A1, the formula =LOWER(A1) will return "excel".

The LEFT function

The LEFT function extracts a specific number of characters from the beginning of a text.

Syntax:

=LEFT(text; number_of_characters)

Example:

If you have "Advanced Excel" in cell A1, the formula =LEFT(A1; 8) will return "Advanced".

The RIGHT function

The RIGHT function extracts a specific number of characters from the end of a text.

Syntax:

=RIGHT(text; number_of_characters)

Example:

If you have "Advanced Excel" in cell A1, the formula =RIGHT(A1; 5) will return "Excel".

The LEN function

The LEN function returns the number of characters in a text.

Syntax:

=LEN(text)

Example:

If you have "Excel" in cell A1, the formula =LEN(A1) will return 5.

The SEARCH function

The SEARCH function finds the position of one text within another text (not case-sensitive).

Syntax:

=SEARCH(find_text; text; [start_position])

Example:

If you have "Advanced Excel" in cell A1, the formula =SEARCH("Excel"; A1) will return 10.

The FIND function

The FIND function works similarly to SEARCH, but is case-sensitive.

Syntax:

=FIND(find_text; text; [start_position])

Example:

If you have "Advanced Excel" in cell A1, the formula =FIND("Excel"; A1) will return 10.

The TEXT function

The TEXT function formats a number as text, with a specific format.

Syntax:

=TEXT(value; text_format)

Example:

If you have the number 1234.567 in cell A1, the formula =TEXT(A1; "$ #,##0.00") will return "$ 1,234.57".

The TRIM function

The TRIM function removes extra spaces from text, leaving only one space between words.

Syntax:

=TRIM(text)

Example:

If you have "Advanced Excel " in cell A1, the formula =TRIM(A1) will return "Advanced Excel".

Practical challenges with text functions

1. Given the table below, extract the first name of each person.

Full Name
John Silva
Maria Oliveira
Pedro Souza

2. Given the same table from Challenge 1, extract the last name of each person.

3. Given the table below, concatenate the first name and last name into a single cell.

First Name	Last Name
John	Silva
Maria	Oliveira
Pedro	Souza

4. Given the table below, replace the word "Excel" with "Google Sheets".

Text
Excel Spreadsheet
Excel Course
Excel Functions

5. Given the table below, extract the domain of each email.

Email
joao@example.com
maria@example.org
pedro@example.net

6. Write the following text in a cell:

"Life is an unpredictable journey, full of challenges, lessons, and unforgettable moments. Through ups and downs, each experience shapes who we are, strengthening us for what is to come. Sometimes plans fail, paths change, and answers seem distant, but it is precisely in uncertainty that we find opportunities for growth. Life is short, time does not wait, and therefore, it is essential to enjoy every moment, value the people around us, and move forward with courage and gratitude. After all, life is not about waiting for the storm to pass, but rather learning to dance in the rain."

How many times does the word life appear in the text?