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)

extract specific data from a cell in excel using left function

Press Enter.

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

autofilling left formula

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)

extract specific data from a cell in excel using right function

Press Enter.

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

auto filling the right formula

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)

extract specific data from a cell in excel using left and find functions

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

extract specific data from a cell in excel using right and left functions

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.

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.

extracting only numbers 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.