## Example – 1 Extracting Specific Text Data from a Cell in Excel through Functions

### Case 1.1 – Extracting Data from the Beginning of a Cell with the LEFT Function

You can Extract Data from the beginning of a cell using the** LEFT function**. We are using the range **B5:B7** as reference data and extracting it to column **C**.

**Steps:**

- In the first result cell (C5), insert the following formula:

`=LEFT(B5,4)`

- Press
**Enter**.

- Click and drag the
**Fill Handle Icon**down to replicate the formula for the rest of the cells.

### Case 1.2 – Extract Data from the End of a Cell with the RIGHT Function

**Steps:**

- In the first result cell (C5), insert the following formula:

`=RIGHT(B5,4)`

- Press
**Enter**.

- Click and drag the
**Fill Handle Icon**to fill up the rest of the cells.

### Case 1.3 – Extract Specific Portions of Data with SEARCH and FIND

We want a specific portion of a cell to be extracted after or before a specific character such as before and after the **@ **sign of an email.

**Extract the Username**

- In the first result cell (C5), insert the following formula:

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

** Breakdown of the Formula:**

**FIND(“@”,B5)**

**The FIND function** 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** extracts the first 12 characters (the result of FIND – 1).

**Extract the Domain Name**

- In the first result cell (C5), insert the following formula:

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

** Breakdown of the Formula:**

**FIND(“@”,B5)**

**The FIND function** returns the numerical value of where the first argument is found. In this case, it would be 13.

**LEN(B5)**

**The LEN function** returns the length or the number of characters in the reference. In this case, it is 22.

**RIGHT(B5,LEN(B5)-FIND(“@”,B5))**

The** RIGHT function** extracts a number of characters from the right, where the number 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

## Example 2 – Extracting Specific Number Data from a Cell in Excel with a TEXTJOIN Formula

Let’s consider a dataset of codes where numbers can be anywhere between the texts. We’ll extract the numbers.

- In the first result cell (C5), insert the following formula:

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

## Example 3 – Extracting Numerical or Text Data from an Alphanumeric Value with Flash Fill

**Steps:**

- Fill up the first cell, manually eliminating all the numbers from the alphanumeric values.

- Start typing the next entry in the row below and Excel will auto-suggest extracted text values from the rest of the columns.

- Press
**Enter**.

- Repeat the same process for numbers.

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

**Download the Practice Workbook**

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