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
Get FREE Advanced Excel Exercises with Solutions!
