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.


Download Practice Workbook

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


3 Examples to Extract Specific Data from a Cell in Excel

1. Extract Specific Text Data from a Cell

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 o FIND functions. Now, in this section, I will guide you through each one with a breakdown when a combination arises.


1.1 Extract 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.

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:

=LEFT(B5,4)

how to extract specific data from a cell in excel

  • Then press Enter.

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

how to extract specific data from a cell in excel


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.

=RIGHT(B5,4)

how to extract specific data from a cell in excel

  • Now press Enter.

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

how to extract specific data from a cell in excel


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.

=LEFT(B5,FIND("@",B5)-1)

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

how to extract specific data from a cell in excel

πŸ”Ž Breakdown of the Formula:

  • FIND(β€œ@”,B5)

FIND function takes two arguments- i) a specific text value or a number and ii) the cell or value from where it will search the first argument. It returns the numerical value of where the first argument is found. In this case, it would be 13.

  • LEFT(B5,FIND(β€œ@”,B5)-1)

The LEFT function takes two arguments – i) a value from where it is extracting, and ii) the length of the string it is extracting, which in this case, is determined from the FIND function and used as a value less from it.

Extract Domain Name

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

=RIGHT(B5,LEN(B5)-FIND("@",B5))

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

how to extract specific data from a cell in excel

πŸ”Ž Breakdown of the Formula:

  • FIND(β€œ@”,B5)

FIND function takes two arguments- i) a specific text value or a number and ii) the cell or value from where it will search the first argument. It returns the numerical value of where the first argument is found. In this case, it would be 13.

  • LEN(B5)

LEN function takes only one argument and returns the length or the number of characters in that cell or text. In this case, it is 22.

  • RIGHT(B5,LEN(B5)-FIND(β€œ@”,B5))

The RIGHT function takes a minimum of two arguments- i) a value from where text is extracted and ii) the length of the extraction from the end. The second argument here was determined by subtracting the values before the @ sign found from the FIND function from the total length of the string found from the LEN function.


2. Extract 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.

=TEXTJOIN("",TRUE,IFERROR((MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)*1),""))

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

how to extract specific data from a cell in excel

πŸ”Ž Breakdown of the Formula:

  • ROW(INDIRECT(β€œ1:”&LEN(B5))) returns an array {1;2;3;4;5;6;7}
  • MID(B5,ROW(INDIRECT(β€œ1:”&LEN(B5))),1) returns the array {β€œa”;”s”;”8β€³;”7β€³;”w”;”q”;”1β€³}
  • IFERROR((MID(B5,ROW(INDIRECT(β€œ1:”&LEN(B5))),1)*1),””) returns the array {β€œβ€;””;8;7;””;””;1}
  • TEXTJOIN(β€œβ€,TRUE,IFERROR((MID(B5,ROW(INDIRECT(β€œ1:”&LEN(B5))),1)*1),””)): In the end TEXTJOIN just concatenates all the values in the array and returns the result.

3. Extract 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.

how to extract specific data from a cell in excel

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

how to extract specific data from a cell in excel

  • Now, press Enter.

how to extract specific data from a cell in excel

  • Repeat the same process for numbers.

how to extract specific data from a cell in excel


Conclusion

These were 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. For more guides like this visit Exceldemy.com.

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo