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

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

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

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

## Related Articles

<< Go Back To Extract Data Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF