There are several methods to Extract Data from Excel Cell. Today we’re going to give you the 5 most effective methods to extract data. You can use any of these methods effortlessly, as we describe these methods step by step in this article.
How to Extract Data from Cell in Excel: 5 Methods
Here, the following Student Information List shows Serial No, Student ID, and E-mail ID. We will use 5 different methods to extract data from the following table’s cell. Here, we have used Excel 365. You can use any available Excel version.
Method 1: Using Text to Columns Feature to Extract Data from Cell
From the following Student Information List table, we want to extract Student ID in two different cells. Here, we want to extract the first name of the student with the number in one cell, and the last name of the student in another cell. The Text to Column feature will be very helpful in that case.
➤ To begin with, we have to select the entire data range of the Student ID column.
➤ After that, we have to go to the Data tab in the Ribbon.
➤ We will click-on Data Tools.
➤ And we will select the Text to Column option.
➤ After that, a window will appear, and we have to select the Delimited option.
➤ Then, we have to click-on Next.
➤ After that, we have to select Tab, and Space options. We will see in the Data Preview that there will be a Tab and Space between the data.
➤ Then, click Next.
➤ After that, we have to give a destination of the data in the Destination box. Here, we want the extracted data in cell F4, therefore, we select cell F4.
➤ Then, click Finish.
➤ Finally, we can see that the Student ID column data is extracted in column Student and ID.
Read More: How to Extract Data from Excel Sheet
Method 2: Several Excel Functions to Extract Data from Cell in Excel
In this method, we will use LEFT, RIGHT, and MID functions to extract data from the Student ID column.
LEFT Function
We will Extract the first name of the Student ID column using the LEFT function.
➤ First of all, we have to type the following formula in cell F5.
=LEFT(C5,4)➤ Here, we have selected the Text in a cell we want to want to extract.
➤ And then, we provided a comma “,”.
➤ Then, we have provided the number of characters from the left of that cell that we want to extract.
Here, as we want to extract the first name of the Student ID column, we select cell C5 as our Text.
➤ After that, we give a comma ”,”.
➤ Then, we type 4, as we want to extract 4 characters from the left.
➤ Now, we can see the first name of the Student ID column in cell F5. We will drag down the function with the Fill Handle tool.
➤ Finally, we can see the first name of the Student ID column in the Student ID First Name column.
RIGHT Function
Here, we will extract the last name of cell C5 from the Student ID list using the RIGHT function.
➤ First, we will type =RIGHT in the cell we want to get the last name of Student ID. Here, we type =RIGHT in cell F4.
➤ After that, we will give a cell reference. Here, we give a cell reference as C5.
➤ Then, we will type 5, as cell C5 has 5 characters from the right. So, the formula will be
=RIGHT(C5,5)
➤ Finally, we can see the last name of the Student ID column in cell F5.
Feel free to utilize Fill Handle to fill all the values.
MID Function
Here, we want to extract the number situated in the middle of the first name and last name of the Student ID column with the MID function.
➤ We will type =MID in cell F5.
➤ After that, we will select cell C5 as text.
➤ Then, we will type 5, as the first number starts from the 5th position.
➤ Then, we will type 3, as the number has 3 characters.
So, our formula will be
=MID(C5,5,3)
➤ Now, we can see in cell F5 only the number from cell C5. We will drag down the function with the Fill Handle tool.
➤ Finally, we can see that all the numbers situated between first name and last name in column Student ID are extracted in column Student ID Middle Number.
Read More: How to Extract Data from a List Using Excel Formula
Method 3: Combination of LEFT and FIND Functions
Here, we want to extract the first part with the name and number before “@” from the column E-mail ID.
➤ First, we have to type =LEFT, and then we have to select D5 as the cell reference.
➤ Then, we will type FIND, and type (“@”,D5)-1), this portion will give us the number of characters we want to extract.
=LEFT(D5,FIND("@",D5)-1)- FIND(“@”,D5) → finds the position of @ within D5
Output: 8
- FIND(“@”,D5)-1 → becomes 8-1
Output: 7
- LEFT(D5,FIND(“@”,D5)-1) → turns into LEFT(D5,7)
Output: Ruth101
➤ Now, we can see the first part of the cell D5 E-mail ID before “@” in cell F5.
➤ We will use the Fill Handle tool to drag down the function.
➤ Finally, we can see the first part of the E-mail ID column is extracted in column First Part of E-mail ID.
Read More: How to Extract Data Based on Criteria from Excel
Method 4: Extract Data Using VLOOKUP Function
Here, we want to extract the e-mail id from E-mail ID column in cell G5 for Serial No 1 using the VLOOKUP function.
➤ First of all, we have to type the following formula in Cell G5.
=VLOOKUP(F5,B4:D12,3,H7)➤ Here, we select F5 as the lookup value, we select B4:D12 as the table array, we type column Index as 3, and we select False exact match.
➤ Now, we can see that the E-mail ID for Serial No 1 is now extracted in cell G5.
➤ We will drag down the function with the Fill Handle tool.
➤ Finally, we can see extracted e-mail id in column E-mail ID.
Read More: How to Extract Specific Data from a Cell in Excel
Method 5: INDEX-MATCH to Extract Data from Cell
Here, we want to extract the e-mail id from E-mail ID column in cell G5 for Serial No 1 using the INDEX & MATCH functions.
➤ To begin with, we will type the following formula in cell G5.
=INDEX(D5:D12,MATCH(F5,B5:B12,0))➤ Here, in the INDEX function, we give array from D5 to D12.
➤ After that, in the MATCH function, we give lookup_value as F5.
➤ We give lookup_array from B5 to B12.
➤ And we select match_type as 0.
➤ After that, we can see the e-mail id of Serial No 1 in cell G5.
➤ We will drag down the function with the Fill Handle tool.
➤ Finally, we can see the extracted e-mail id in column E-mail ID.
Download Workbook
Conclusion
Here, we tried to show you 5 easy and effective methods that will help you to Extract Data from Excel Cell. We hope you will find this article helpful. If you have any queries or suggestions, please feel free to contact us in the comment section.
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!
