How to Extract Specific Data from a Cell in Excel (3 Examples)

Excel provides different ways to extract different data, mostly relying on different functions. Some may be extracted directly from specific functions while others can take a bit more complicated form from a combination of functions. In this article, I will try to show you how to extract a specific kind of data, for each type, from a cell in Excel.

1. Extracting Specific Text Data from a Cell in Excel

Excel provides different functions to extract text from different portions of the information given in a cell. You can extract text from a long text string by using LEFT, RIGHT, MID functions or a combination of these and SEARCH or FIND functions. Now, in this section, I will guide you through each one with a breakdown when a combination arises.

1.1 Extracting Data from the Beginning of a Cell

You can Extract Data from the beginning of a cell using the LEFT function. This function takes two arguments- i) the text you want to extract from and ii) the number of characters you want to extract from a cell.

I am using an array shown below. I am using the range B5:B7 as reference data and extracting it to column C.

Steps:

• In the cell, you want to write your extracted data (in this case it is cell C5), write down the following formula:

• Then press Enter.

• Now, click and drag the Fill Handle Icon down to replicate the formula for the rest of the cells.

1.2 Extract Data from the End of a Cell

To extract data from the end of the cell you need to use the RIGHT function. I am using the same array shown in the previous section to illustrate extracting data by using the RIGHT function.

Steps:

• In cell C5 (or the cell you want to extract to), type in the following formula.

• Now press Enter.

• Then click and drag the Fill Handle Icon to fill up the rest of the cells.

1.3 Extract Specific Portions of Data

Let’s say we want a specific portion of a cell to be extracted usually after or before a specific character such as before and after the @ sign of an email. We can use it in combination with the SEARCH function or the FIND function. Both share the same purpose in this context. Here, I am using the FIND function.

For this section let’s take a dataset comprising of emails.

Extract Username

To extract the username (the portion before the @ sign) use this formula to extract it out.

Now press Enter. Then use the Fill Handle Icon to fill up the rest of the cells replicating this formula.

Extract Domain Name

To extract the domain name (the part after the @ sign) use the following formula.

Then press Enter and fill out the rest using Fill Handle Icon just like above.

Read More: How to Extract Data from Excel Sheet 

2. Extracting Specific Number Data from a Cell in Excel

Now, let’s consider a dataset of codes that can be a mixed bag, where numbers can be anywhere between the texts.

In this method, we are going to use the TEXTJOIN function to concatenate all the numbers extracted individually from each value. To extract the numbers a combination of auxiliary functions such as LEN, INDIRECT, ROW, MID, and IFERROR is used. These auxiliary functions are used to create an array from each value, consisting of all the numbers and the string values replaced by an empty string. And finally, the TEXTJOIN function just helps to put together all into one value.

To extract only numbers out of this array, write down the following formula in the cell.

Press Enter and click and drag the Fill Handle Icon to fill out the rest of the cells you want to be replicated.

Read More: How to Extract Data Based on Criteria from Excel

3. Extracting Specific Number and Text Data from Alphanumerics

If you are having trouble using the formulas for your worksheet or having difficulties grasping the formulas mentioned above you can follow this automated method Excel provides.

Steps:

• For texts, fill up the first cell manually eliminating all the numbers from the alphanumeric values.

• Start typing the one below and Excel will auto-suggest extracted text values from the rest of the columns.

• Now, press Enter.

• Repeat the same process for numbers.

Read More: How to Extract Data from a List Using Excel Formula 

Download Practice Workbook

Download and Practice the workbook with all the example datasets used in this article with formulas included.

Conclusion

These are the methods you can use to extract specific data from a cell in Excel. I hope the illustrations and breakdowns were helpful for you to understand the formulas and implement them for your problems.

Related Articles

• Excel Formula to Get First 3 Characters from a Cell
• How to Extract Month and Day from Date in Excel
• How to Extract Month from Date in Excel
• How to Extract Year from Date in Excel
• How to Extract Data From Table Based on Multiple Criteria in Excel

<< Go Back To Extract Data Excel | Learn Excel