This will remove all the space characters in the selected data set. Select the cells from which you want to remove the space character.To remove all the space characters in a data set, follow the below steps: In such cases, you can do this again to remove and any double spaces that might have been left. If you have three space characters in between 2 words, it would result in 2 space characters (would remove one). Note that this will only remove double spaces. This will replace all the double spaces with a single space character. In the Find and Replace dialogue box, enter:.(You can also use the keyboard shortcut – Control + H). Go to Home –> Find & Select –> Replace.Select the cells from which you want to remove double spaces.It will find and replace double spaces irrespective of its position. Note that this technique cannot be used to remove leading or trailing spaces. When you want to remove all the space characters.This is a faster technique and can be useful in the given situations: You can remove spaces in Excel using the Find and Replace functionality. So, if you want to be absolutely sure that you have all the extra spaces and non-printing characters, use the below formula: =TRIM(CLEAN( SUBSTITUTE(A1,CHAR(160)," "))) Remove Extra Spaces in Excel using FIND and REPLACE Non-printing characters can also result from =CHAR(160), which can not be removed by the CLEAN formula. If you have some text in cell A1 from which you want to remove spaces, use the below formula: =TRIM(CLEAN(A1)) To remove non-printing characters, you can use a combination of TRIM and CLEAN functions. It would remove the extra spaces such that the result always have one space character between words.Įxcel TRIM function does a good job in removing spaces in Excel, however, it fails when you have non-printing characters (such as line breaks) in your data set. This function is also helpful if you have more than one space character between words. Once you have the cleaned data, copy it paste it as values in place of the original data.
In this tutorial, I will show you two ways to remove spaces in Excel.Įxcel TRIM function removes the leading and trailing spaces, and double spaces between text strings.įor example, in the above example, to remove spaces from the entire list if first names (in A2:A7), use the following formula in cell C1 and drag it down for all the first names: =TRIM(A2)Įxcel TRIM function would instantly remove all the leading and trailing spaces in the cell.
DOWNLOAD EXCEL DUPLICATE REMOVER FOR MAC DOWNLOAD
There are many ways you can end up with these extra spaces – for example, as a part of the data download from a database, while copying data from a text document, or entered manually by mistake. I can’t think of a situation where you may need these extra spaces, but it often finds its way into the excel spreadsheets.
Leading and trailing spaces in Excel often leads to a lot of frustration.
0 kommentar(er)
