We'll show you an all-exclusive formula below to eliminate the following characters:
The SUBSTITUTE function here is being used for two things to show you how to use SUBSTITUTE to remove nonbreaking spaces (a type of nonprinting character) from text and to remove the single spaces leftover from the TRIM function. The SUBSTITUTE function replaces existing text with new text in a text string. Use CLEAN, TRIM, & SUBSTITUTE Functions to Remove All the Spaces from a Cell (except single word spacing)Ī quick recap the TRIM function is for the extra spaces and the CLEAN function is for nonprinting spaces. Hence, we recommend the above formula for consistent results. Using the CLEAN function later would rid the cell of both of these but may sometimes leave a space behind contributing to needless extra spaces in the results. Using the TRIM function first would leave the line breaks and nonprinting characters.
You may think the formula would still work with the functions switched the other way round but we suggest you stick with this order. The result is a tidied-up cell of text with the right spaces. The CLEAN function clears B3 of all the line breaks and nonprinting characters, leaving all the extra spaces to be passed onto the TRIM function for clear-up.
The TRIM and CLEAN functions have been paired together to work on one argument cell B3. Now let's watch the TRIM function removing leading and trailing spaces and extra word spacing. If you aim to remove all space characters from a cell, head to the last couple segments of this guide. This implies that if there are accidental extra spaces breaking a word, the function will eliminate the extra spaces but will treat one space as required. The TRIM function will remove all leading and trailing spaces and extra word spacing. The TRIM function removes all spaces from a text string except for single spaces between words. So let's save the sheet aesthetics and analysis distress and learn how to trim away the unwanted characters. While data mining or analyzing, it would be hard to trace text out because we'd be looking for "A" but couldn't find it because the text in the cell is " space characterA".
But first, let's find out what these are and why are they a menace for our worksheets. Some data anomalies can be fixed with Flash Fill but as other wholesome solutions, we'll teach you how to remove extra spaces, line breaks, and nonprinting characters from cells. This tutorial is about getting your data in the right shape in Excel. Those characters and spaces that wouldn't go no matter how much you've tried to maneuver them away without having to handpick them? Don't worry, we're not asking you to handpick them either. If data is split into lines from where it is being copied, it might be pasted with many space characters or line breaks and sometimes we don't just carry space characters, we may have introduced our sheets to other characters. Such Excel pandemonium is the result of badly copied data (or badly pasted, more like). Yes, you have your work cut out for you but we'll try to make it as breezy as possible. Spaces are where they shouldn't be, line breaks are stretching text all the way down, some symbols thrown in for good confusing measure. You know what kind of data looks like a big piece of work the moment you see it everything is so haywire and all over the place.