How to Remove Partial Data from Multiple Cells in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

As we export data from various sources it doesn’t come with a structured format. Depending on your need you may create new types of datasets to give a format or perform different functions. In this article, I’m going to explain how to remove partial data from multiple cells in Excel.

To make the explanation understandable I’m going to use a dataset that represents product information about a particular fruit shop, but some pieces of information are compact. There are 3 columns in the dataset; these are Product & ID, Order Date, and Delivery Details.

Sample dataset to Remove Partial Data from Multiple Cells


6 Ways to Remove Partial Data from Multiple Cells in Excel

1. Using Flash Fill to Remove Partial Data from Multiple Cells

In case you have some compacted values in your dataset then you can use the Flash Fill command to remove partial data from multiple cells in Excel.

To use the Flash Fill command first, you will need to create a pattern to follow.

Here, I’ve created a pattern of delivery Order Id by removing the Product part from the Product & ID column.

Next, open the Data tab >> select Flash Fill

Using Flash Fill to Remove Partial Data from Multiple Cells

Now, the partial data from the Product & ID column are removed by following the given pattern.

Read More: Using Excel to Clean and Prepare Data for Analysis


2. Using Find & Replace Feature

If you want to remove the same kinds of data partially from multiple cells the Find & Replace feature is so useful.

Let’s demonstrate to you the use of this feature.

➤ First, open the Home tab >> go to Editing group >> from Find & Select >> select Replace

Using Find & Replace to Remove Partial Data from Multiple Cells

➤ A dialog box of Find and Replace will pop up.

From there in Find what, provide the character you want to find to remove.

➤ I’ve provided the character (_*) in Find what. Here, I used an asterisk (*) after the underscore (_) so that I get only the data of Product name and delivery Status from the respective Product & ID and Delivery Details columns.

➤ As I only want the Product name and delivery Status so kept the Replace with blank.

➤ Then, click Find All.

Now, you will see which cell’s value contains the given character.

➤ Then, click Replace All.

Using Find & Replace to Remove Partial Data from Multiple Cells

A message about how many replacements occurred will pop up.

➤ Here, it will show 18 replacements.

Hence, you will get the Product name and delivery Status from the respective Product & ID and Delivery Details columns.

Using Find & Replace to Remove Partial Data from Multiple Cells

Read More: How to Clean Up Raw Data in Excel


3. Using Text to Columns Option

One of the best ways to remove partial data from multiple cells is the Text to Columns command. You can use the Text to Columns command from the ribbon.

To begin with, first, select the cell range from where you want to remove partial data.

➤ I selected cell range B4:B12

➤ Open the Data tab >> then select Text to Columns

Using Text to Columns to Remove Partial Data from Multiple Cells

➤ A dialog box will pop up.

Now, choose the data type

➤ I’ve chosen Delimited then click Next

➤ Another dialog box will pop up

Here, select the Delimiters your data has.

➤ I selected underscore (_) in the Other option as my data has this character.

If you want, you can see how your data will be split in the Data preview.

➤ Again, click Next.

Using Text to Columns to Remove Partial Data from Multiple Cells

➤ Again a dialog box will pop up.

From there, select the destination of your choice to place the separated data.

➤ I selected the E4 cell.

➤ Finally, click Finish.

Here, you will see the data of the Product & Id column are separated into 2 new columns.

Using Text to Columns to Remove Partial Data from Multiple Cells

Now you can delete any of the parts of the data you want.

Read More: How to Clean Survey Data in Excel


4. Using the RIGHT Function to Remove Partial Data from Multiple Cells

You can use the RIGHT function to remove partial data from multiple cells.

Now, from the Product & ID column, I will extract only the Order Id using the RIGHT function. 

Let me show you how you can use the RIGHT function.

First, select a cell where you want to keep your resultant value.

➤ I selected the E4 cell.

Then, type the following formula in the selected cell or into the Formula Bar.

=RIGHT(B4:B12, 4)

Using the RIGHT Function to Remove Partial Data from Multiple Cells

Here, in the RIGHT function selected the cell range B4:B12 as text, and in num_chars given 4 as I want to extract 4 characters from the right.

➤ Press the ENTER key. Now, you’ll get the right 4 characters for all the selected cell ranges which are Order Id.

Read More: Data Cleaning Techniques in Excel


5. Using the LEFT & SEARCH Functions in Excel

You also can use the SEARCH function with the LEFT function to remove partial data from multiple cells.

To demonstrate the procedure, from the Delivery Details column I will extract only the left value which will be delivery Status.

Let’s jump into the procedure,

First, select a cell where you want to keep your resultant value.

➤ I selected the E4 cell.

Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(D4:D12, SEARCH("_at_", D4:D12) -1)

Using the LEFT & SEARCH Function to Remove Partial Data from Multiple Cells

Formula Breakdown

  • Here, the SEARCH function will search the given character _at_ as I used it as find_text.In the within_text selected the D4:D12 cell so that it searches the selected text within the given cell then subtracted 1 character.
  • After getting the position number by using the SEARCH function, the LEFT function will use it as num_chars and also select the D4:D12 cell to extract the left value from the searched character.

➤ In the end, press the ENTER key.

Now, you’ll get the partial data from the Delivery Details column which will be the delivery Status.


6. Using the LEFT, FIND & SUBSTITUTE Functions in Excel

By using the FIND function and the SUBSTITUTE function in the LEFT function you can remove partial data from multiple cells.

For demonstration purposes, will use the Order Info column to extract the partial data which will be the Product & ID name.

Using the LEFT, FIND & SUBSTITUTE Function to Remove Partial Data from Multiple Cells

First, select a cell where you want to keep your resultant value.

➤ I selected the E4 cell.

Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(B4:B12,FIND("_",SUBSTITUTE(B4:B12,",","_",2))-1)

Formula Breakdown

  • Here, the FIND function will find the given character underscore (_) as I used it as find_text.In the within_text used the SUBSTITUTE function because the data have multiple commas.
  • Then, selected the cell range B4:B12 as text, given the 2nd comma (,) as old_text, then used (_) as new_text so that it substitutes the second comma with an underscore. Then subtracted 1 character from the findings of FIND.
  • Now, in the LEFT function, I used the position of the second comma as num_chars then select the B4:B12 cell to extract the left value from the second comma.

This is very useful when your data have multiple commas or any special character.

➤ Press the ENTER key. Now, you’ll get the partial data from the Order Info column which will be the Product & ID.

Using the LEFT, FIND & SUBSTITUTE Function to Remove Partial Data from Multiple Cells


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.


Download Practice Workbook


Conclusion

In this article, I’ve explained 6 different methods to remove partial data from multiple cells in Excel. You can follow any of the methods of your need and choice. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

<< Go Back To Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo