Farihat
Organizing data using various formulas is quite common in Microsoft Excel. Usually, records are organized in a tabular form. After entering data, you might need to remove or separate the strings or specific characters. These are your partial data. You can edit a few of the records. But in cases of a big dataset, you should consider Excel formulas or shortcut techniques to remove partial data from multiple cells in Excel. This article will help you in seven ways to guide you out of the issue. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Remove Partial Data from Multiple Cells in Excel with These 7 Approaches
The article is going to introduce seven approaches in Excel to remove partial data from multiple cells. The methods will help you understand the uses elaborately. The applications will cover a method for specific character removal and three uses of options such as Flash Fill, Find & Replace (Ctrl+H), Text to Column. You will find uses of functions like RIGHT, LEFT, SEARCH, FIND, SUBSTITUTE for other methods. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.
1. Using SUBSTITUTE Function
To remove a specific character from the string, you can use the SUBSTITUTE function. You need to specify the character within the formula. Following is an example to create new ID numbers without the hashtag in between.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select E6.
- Secondly, enter the following formula.
- Finally, copy the formula from E6 to E7 and E8 by Fill Handle icon.
2. Applying Flash Fill Option
Flash Fill is a fantastic tool that can follow a selected cell and repeat the action for all the available cells itself. Here, all you need to do is write your requirement once and Flash Fill will do the rest. Following is an example to keep the email ID initials from the email IDs.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell E6.
- Then, write the data format that you want for all the cells.
- Next, select the Flash Fill option, from the Data tab.
- Upon the option selection, Excel will automatically show you results for other available cells as well. Moreover, it will also allow you to use Flash Fill in any other way you may want to use.
3. Utilizing Find & Replace Command
From the Home tab, Find & Replace is an easy way to remove partial data from multiple cells in Excel. It doesn’t depend on any range. Additionally, this method works on all the active cells of an entire worksheet. Here, you will see an example to keep the email ID initials from the email IDs.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, press Ctrl+H. You can also go to the Replace under the Find & Replace option, from the Home tab’s editing group.
- Now, as visible in the image below, type “#*” on the Find bar. Keep the Replace With bar blank.
- Next, choose the Find All option. The Find All option will bring all the available results from the entire worksheet.
- Then, tap on the Replace All option.
- A dialogue box on the success of the process will pop up. Click OK.
- All your data will automatically remove the partial data from multiple cells in Excel.
📕 Read More: 3 Approaches to Remove Blank Rows in Excel Pivot Table
4. Employing Text to Column Feature
Text to Column is a complex process. But, the variety of options it offers, beats any other methods discussed before. You can get results out of it in 3 steps. Each step, along with the example to get the ID code and digits individually is briefly discussed.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell range C6:C8.
- Secondly, select Text to Column from the Data tab.
- In the 1st step, choose Delimited as the File type, will get a preview of the result.
- Now, click on the Next button.
- Now, choose the Other as Delimiters (or any of the given options, if you have them in your data range).
- After that, type “#” on the blank box beside. You should get a preview as well.
- Then, press the Next button again.
- Choose General as the Data format (choose any other if you want to designate them beforehand).
- Choose the cell ID where you want the new data. For the image, choose E6.
- Now, click on the Finish button.
- You should get your data individually like in the following picture.
5. Applying RIGHT Function
The RIGHT function can return characters as directed in the formula. Following is an example of how you can get only the ID digits from the ID numbers.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cell ID E6.
- Now, enter the following formula here.
- Finally, copy the formula from E6 to E7 and E8 by Fill Handle icon.
6. Combining LEFT and SEARCH Function
We can use the LEFT function to get characters from cells according to the allocated number from the left side. The SEARCH function can work within it and can help to remove partial data from multiple cells in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell ID E6.
- Now, enter the following formula here.
- Here, copy the formula from E6 to E7 and E8 by Fill Handle icon.
🔨 Formula Breakdown
LEFT(D6, SEARCH(“@”, D6) -1)
👉 Here, SEARCH(“@”, D6) returns the data that is after “@” character.
👉 Also, LEFT(D6, SEARCH(“@”, D6) -1) shows the data after removing the previous value.
7. Applying LEFT Function for Multiple Criteria
You can create a combination using the LEFT, FIND and SUBSTITUTE functions. Following is an example that allows the cells to remove partial data of two categories all at once.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell ID E6.
- Now, enter the following formula here.
- Here, copy the formula from E6 to E7 and E8 by Fill Handle icon.
🔨 Formula Breakdown
LEFT(C6,FIND(“_”,SUBSTITUTE(C6,”#”,”_”,2))-1)
👉 Here, SUBSTITUTE(C6,”#”,”_”,2) returns the data after removing the Hashtag character.
👉 Again, FIND(“_”,SUBSTITUTE(C6,”#”,”_”,2)) gets the data for LEFT. It comes after removing the Underscore character.
👉 Finally, LEFT(C6,FIND(“_”,SUBSTITUTE(C6,”#”,”_”,2))-1) gets the data left after all the removal according to the number from the left side.
📄 Important Notes
🖊️ The RIGHT and the LEFT function is not applicable for ranges. Make sure to use them as individual cells or texts.
🖊️ Flash Fill is available for Microsoft Excel 2013 version or higher than that.
📝 Takeaways from This Article
The article allows the readers to understand the variety of options available to remove partial data from multiple cells in Excel.
📌 Primarily, you can use the SUBSTITUTE function to remove specific characters. It can even work within a string.
📌 Flash Fill can automatically get you removed from partial data. It works in an instant for adjacent cells.
📌 Find & Replace is the only option that has no range boundary. Applying the method will ensure the specific removal of the entire worksheet.
📌 Text to Column is one of the long and complex methods. But it can get you precise results just as you want.
📌 RIGHT function gets an amount of character from the right side of a specific cell.
📌 You can remove partial data from multiple cells in Excel with the help of the LEFT function and SEARCH function. The formula detects the removal and the rest of the data comes as the result.
📌 To remove partial data from multiple cells in Excel, you can add multiple criteria. With the help of a combination of the LEFT, FIND, SUBSTITUTE functions, you can successfully do it.
Conclusion
To remove partial data from multiple cells in Excel, seven methods are useful. You can use the SUBSTITUTE function to remove specific characters within the string. There are three useful options such as Flash Fill, Find & Replace (Ctrl+H), Text to Column. You will find uses of functions like RIGHT, LEFT, SEARCH, FIND and SUBSTITUTE for other methods. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
Related Articles
- 4 Easy Tricks to Remove Table from Excel
- How to Convert Formula to Value for Multiple Cells in Excel
