How to Extract Data from Cell in Excel (5 Methods)

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.


Download Workbook


5 Methods to Extract Data from Cell in Excel

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.

How to Extract Data from Cell in Excel


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.

Extract Data from a Cell in Excel using Text to Columns feature

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

Extract Data from a Cell in Excel using Text to Column Feature

Read More: How to Extract Specific Data from a Cell in Excel (3 Examples)


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.

Extract Data from a Cell in Excel using functions

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.

Extract Data from a Cell in Excel using LEFT function

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

Extract Data from a Cell in Excel using RIGHT function

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.

Extract Data from a Cell in Excel using MID function

Read More: How to Extract Data from a List Using Excel Formula (5 Methods)


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.

Extract Data from a Cell in Excel using LEFT and FIND function

Read More: How to Extract Data from Excel Based on Criteria (5 Ways)


Similar Readings


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.

Extract Data from a Cell in Excel using VLOOKUP function

Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP


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.

Extract Data from a Cell in Excel using INDEX-MATCH function

Read More: Excel Formula to Get First 3 Characters from a Cell(6 Ways)


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 know us in the comment section. And please visit our blog for more such articles.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo