How to extract certain text from a cell in Excel

Excel is used to complete a wide range of activities, including data organisation and filtering, graph and chart creation, and information search. Excel gives you the option to do a process known as substring extraction, which involves taking a special part of text from one cell and displaying it in another cell. We will go through how to extract a substring in Excel in this tutorial.

The following screen shot, for example, shows how to select all words containing the = character from a cell that includes a specified character or text. How would you approach this Excel problem?

You should construct a formula using the TRIM, MID, SUBSTITUTE, REPT, MAX, and FIND functions to resolve this issue. This is the general syntax:

Formula: 

=TRIM(MID(SUBSTITUTE(A1, ,REPT( ,99)),MAX(1,FIND(@,SUBSTITUTE(A1, ,REPT( ,99)))-50),99))

REPT: With the REPT function, characters can be repeated a predetermined number of times.

SUBSTITUTE: The SUBSTITUTE function swaps out one text or character for another within a text string.

TRIM: The TRIM function only leaves single spaces between words after removing all excess spaces from a text string.

MID: The middle characters of a text string are returned by the MID function.

MAX: The MAX function finds the greatest numerical number within a set of possible values.

Summary:

You may apply a formula based on numerous functions, such as TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT to extract a word that includes specified content. Email addresses and other substrings with a distinct id may be extracted using this algorithm.

The formula in C5 in the illustration is as follows:

=TRIM(MID(SUBSTITUTE(B5, ,REPT( ,99)),MAX(1,FIND(@,SUBSTITUTE(B5, ,REPT( ,99)))-50),99))

Explanation:

The basic idea is that this formula discovers and extracts the desired substring from a text string by flooding the gaps between words in the text string with several spaces, then utilises the TRIM function to clean up the mess.

Using SUBSTITUTE:, fill the original text in B5 with spaces starting from the inside out.

SUBSTITUTE(B5, ,REPT( ,99))

This substitutes 99 spaces for every single space.

Note: The longest word you must extract is 99, which is merely a random number.

Then, inside the inundated text, the FIND function finds the particular character (in this example, @).

FIND(@,SUBSTITUTE(B5, ,REPT( ,99)))-50

After deducting 50, FIND returns the location of the @ in this text. By deducting 50, the location is essentially walked back to fall somewhere in the centre of the spaces before the substring of value. The computed position in the displayed example is 366.

The issue of the substring showing first in the string is dealt with by the MAX function. The position will then be negative, and MAX is utilised to return to 1.

After establishing a starting position, MID is used to pull 99 characters of text, beginning at 366 from the text in B5, which is once more drenched in space:

MID(SUBSTITUTE(B5, ,REPT( ,99)),366,99)

With a large number of space characters before and after, this recovers the correct substring.

The TRIM function, which produces a substring holding the special character after trimming the preceding and following spaces, is the last step.

Did you learn about how to extract certain text from a cell in Excel? 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, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience