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:

`=LEFT(B5,4)`

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

`=RIGHT(B5,4)`

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

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

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

**🔎 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.

**🔎 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.**

**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.

`=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.

**🔎 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.

**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**