WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to Extract Text After a character in Spreadsheets

August 1, 2022
8.1K Views

In Spreadsheets, the performance of every task has its own function. And if you are dealing with a difficult data sheet. You may find it troubling when you have to extract data on the right for a specific character. Here, you will be learning to extract after a character in a spreadsheet.

Now, there are several ways to perform this function which are as follows:

Manage to Extract Text by using Mid and Find Function:

Here, we are using both the Find function and Mid function respectively. Firstly, we will find a certain character from the cell with the usage of the Find function. Later we will be extracting text from a specific position of the cell.

The data set is given below:

Now, our data is in the data set. And the cell has a comma (“,”) in it. Now, our task is to separate by extracting the text after a certain character comma (“,”) by using a formula.

FORMULA:

=MID(B6,FIND(,,B6)+1,LEN(B6))

Insert the formula in the cell and press enterUse the Fill handle icon over C6:C11, or drag it.

You have successfully extracted text from a cell after a certain character.

Manage to extract text after a character using RIGHT, LEN and FIND Functions:

In this function, we are using the RIGHT function, LEN function, and FIND function, respectively.

Here, we are fulfilling our task by extracting a sub-string from a cell after a word or character.

Insert the given formula in cell C6.

FORMULA:

=RIGHT(B6,LEN(B6)-FIND(,,B6))

1. Click enter key

2. Use the Fill handle icon over the cell range of C6:C11 or drag cells below.

You have learned another method to successfully extract text after a character.

Manage to Extract text after a specific character by using LEFT, FIND and SUBSTITUE Functions:

Here, we are using the LEFT, FIND and SUBSTITUTE Functions in our Excel spreadsheet. And our data set is given below:

Again we are using the same data set but with the addition of several characters. As this spreadsheet is now more interesting due to the multiple characters in every cell. Our task is to extract text from the cell after those particular characters bu using the given formula:

FORMULA:

=SUBSTITUTE(B6,LEFT(B6,FIND(C6,B6)),)

1. Insert the Formula in cell D6.

2. Press the Enter key.

3. Use the Fill handle over D6:D11, or drag it to the desired cell.

You now know another method to extract text after character in Excel.

Manage to learn RIGHT SUBSTITUE, and REPT Functions to extract text after a character:

In this method, our main formula consists of three functions to extract text that is the RIGHT Function, SUBSTITUTE Function, and REPT Function.

We begin this method using the following steps:

Insert the given formula in the cell D6.

Formula

=TRIM(RIGHT(SUBSTITUTE(B6,C6,REPT( ,LEN(B6))),LEN(B6)))

Press the enter key.Drag through out the desired cell or do use Fill Handle for the cell D6:D11.

Note: we have used trim function to deduct extra space in leading.

Did you learn about how extract text after character. You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.