#Delete duplicate cells in excel for mac download#

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.

Remove Extra Spaces in Excel using FIND and REPLACE.To be on the safe side, it is always a good idea to clean your data and remove spaces in Excel. In this example, it was easy to spot the issue in such a small data set, but imagine having to check this for thousands of records. You might not be able to spot the difference with the naked eye that there is an extra trailing space in the name that is causing this error. Now have a look at what happens when I use a VLOOKUP function to get the last name using the first name. Leading, trailing, or double space between text could lead to a lot of serious issues.įor example, suppose you have a data set as shown below:

#Delete duplicate cells in excel 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. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.Leading and trailing spaces in Excel often leads to a lot of frustration. Rather than a single cell address, you'll refer to a cell range using the cell address of the first and last cells in the cell range, separated by a colon. A group of cells is known as a cell range. You can also select multiple cells at the same time. Note that a cell's column and row headings become darker when the cell is selected. In this example, the selected cell intersects column C and row 10, so the cell address is C10.

Columns are identified by letters (A, B, C), while rows are identified by numbers (1, 2, 3).Įach cell has its own name-or cell address-based on its column and row. A cell is the intersection of a row and a column. Cell basicsĮvery spreadsheet is made up of thousands of rectangles, which are called cells. The saved changes notification lets you know your spreadsheet has been saved. Google Sheets automatically saves your changes as you work.