How to get text from cell in Excel

The TEXT Functions in Excel are quite powerful. These routines allow you to do a variety of text slice and dice operations.

The extraction of a substring in Excel is one of the frequent chores for those dealing with text data (i.e., get part of the text from a cell).

Unfortunately, Excel doesn't have a simple substring function that can accomplish this. However, text formulas and a few other built-in Excel tools might still be used to get text from cell in Excel.

TEXT Functions in Excel:

It would be really simple to recover a substring from the source text in Excel because to its variety of text operations. The Excel Text functions that will be used in this lesson are listed below:

The RIGHT function removes the supplied characters from the text string's right side.

The LEFT function removes the number of characters from the text string's leftmost position.

The MID function takes a text string and extracts the requested number of characters from the supplied beginning point.

Finds the beginning of the provided text in the text string using the FIND function.

The LEN function returns the length of the text string in characters.

Example 1: Get text from cell in Excel

Finding patterns is crucial when utilising Text functions (if any). That makes creating a formula really simple. The @ symbol appears between the username and the domain name in the example above, and we will use that pattern as a guide to determine the usernames.

The method to determine a username is as follows:

=LEFT(A2,FIND(@,A2)-1)

The above formula locates the location of the @ symbol in the id and uses the LEFT function to extract the username. The FIND function is used for this, and it returns the location of the @.

For instance, FIND(@,A2) would return 11, which is the location of brucewayne@batman.com in the text string.

The LEFT method is now used to remove 10 characters from the string's left side (one less than the actual value that is returned by the LEFT function).

Example 2: Get text from cell in Excel

The domain name may be obtained using the same reasoning as in the aforementioned case. The only difference in this case is that we must remove the characters from the text string's right side.

The equation to do this is as follows:

=RIGHT(A2,LEN(A2)-FIND(@,A2)

The same logic is applied in the formula above, but it is modified to ensure that we are obtaining the right string.

Let's use Bruce Wayne from Batman.com as an example once more. The @ sign's current location, which in this example is 11, is returned by the FIND function. We must now remove every character that follows the @. Therefore, we calculate the string's overall length and deduct the characters up until the @. We can see how many characters are used to cover the domain name on the right.

We can now easily obtain the domain name by using the RIGHT function.

Example 3: Get text from cell in Excel

You must locate the markers immediately before and after the substring in order to extract it from the centre of a text string.

For instance, in the case below, the indicator would be @ (that is immediately before the domain name) and to obtain the domain name without the.com portion (which is right after it).

The following equation will just extract the domain name:

=MID(A2,FIND(@,A2)+1,FIND(.,A2)-FIND(@,A2)-1) 

Did you learn about how to get text from a cell in an Excel file? 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