![]() Once calculated, if we want, can copy the values in cells F3:F10 and paste the values into cells C3:C10. The result is that the spaces have been removed. Let’s head back and see how we can use this with our Supplier name column. The TRIM function has a single argument, which is the text from which to remove spaces. Reduces multiple spaces to a single space.TRIM Function to remove excess spaces (#3) We simply apply the formula to the new data and it recalculates automatically. If we are looking for a more dynamic method of removing unnecessary spaces, then functions such as TRIM or SUBSTITUTE may be a better solution.įormulas are useful if we are likely to get new data and have to perform the space removal process again. Note: If Excel cannot find a matching pattern, it will return an error message. In the screenshot below, column F shows the clean data. We need to keep adding more examples for Flash Fill to use.ĭone! Because we rely on Excel’s algorithm, we should check the other values to ensure they are right. However, in our example, you will notice the period between Spares and com has been removed. If you are happy with the recommendations, press the Tab key to accept them. Then, when we start typing a second value, hopefully, Flash Fill will spring into action and make suggestions. With our data on the left, we can type how the first value should display. So, you may have used Flash Fill before without knowing it. It’s a feature that runs in the background and makes recommendations at suitable points. ![]() It is easy to use and ideal for finding patterns in text strings. Flash Fill (#2)įlash Fill was introduced in Excel 2013. Repeat these steps multiple times until there are no more items to replace. If our data contains double spaces that need to be removed, in the Find & Replace dialog box enter the following: Obviously, if our data should contain single spaces, such as a supplier name, this approach won’t work. In the Find and Replace dialog box, enter a space into the find what field, and leave the replace with field blank.Click Home > Find & Replace (dropdown) > Replace… (Shortcut: Ctrl + H).Select the range of cells to be changed (Cells B3-B10 in our example).In our example data, we have an issue with the Part Number column it contains lots of rogue spaces. If our data contains leading, trailing, or mid-spaces that all need to be removed, the Find and Replace method is perfect. Find and Replace (#1)įind and replace may seem obvious, but you would be surprised about the number of people who don’t think about it. Therefore, they are great for one-off activities. These are fast and quick methods but need to be repeated if data changes. We can use simple Excel functionality to remove additional spaces, such as Find and Replace or Flash Fill. SUBSTITUTE Function to replace spaces (#4).TRIM Function to remove excess spaces (#3).Remove spaces in Excel – simple methods.
0 Comments
Leave a Reply. |