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

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


Download to Practice


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: How to Remove Numbers from a Cell in Excel (7 Effective Ways)


2. Using Find & Replace

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 of 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 Find and Delete Rows in Excel (5 Ways)


3. Using Text to Columns

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: Data clean-up techniques in Excel: Replacing or removing text in cells


Similar Readings


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: How to Delete Blank Cells and Shift Data Left in Excel (3 Methods)


5. Using the LEFT & SEARCH Function

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

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 Function

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

For the demonstration purpose, 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)

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.

Here, 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.

Read More: How to Clear Formula in Excel (7+ Methods)


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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo