By setting CELLSPACING to zero, you can remove all the space. Right-click within one of the selected cells, and choose Delete in the drop-down menu that appears.Įxcel will now ask which direction you want it to shift the cells that remain. The space between the table cells is controlled by the CELLSPACING attribute in the TABLE tag. Use the syntax TRIM (cell) and press Enter. The spreadsheet should now look like this, with only the blank cells selected. This function removes all spaces from the selected cells except for single spaces between words. Selecting Replace All will eliminate all the spaces in each row. In the Go To Special dialog box, choose Blanks and click OK. In the Find what field input a space and leave the Replace with field blank Select Replace All. The formula is a combination of TRIM, CLEAN, SUBSTITUTE & CHAR functions. Simply type TRIM and enter your cell reference. Press F5 and click Special in the Go To dialog that appears. However, TRIM in Excel does not remove trailing spaces in Excel. Using the trim function will help you eliminate the spaces. Select the data range where there are blanks to remove.

Here is a typical import… We have Employee Names, their Roles, and their Hire Dates, but they are not in the proper columns… Let’s walk through an example of how… Examine the Data Fortunately, there is a way to select all the blanks in a selection simultaneously for deletion. You can also use the keyboard shortcut Ctrl + H for this action. This will open the Find and Replace dialogue box. In the Home tab's Editing group, click on the Find & Select button and select Replace from the list. At first glance, this looks like it could be a very tedious problem to solve… It could take ages to manually take them out, and sometimes the data order is sensitive, so it can’t be sorted (to bring all the blanks together). Select the cells from where you want to remove the spaces. Press ENTER and You’ll find the text without blank spaces in cell A16. Sometimes, all the information is there, but extra blanks and spaces are carried along with the data cells. Type the following formula in an empty cell ( A16 ), Here, the TRIM function will remove extra blank spaces from the selected cell A5. To do this, select all of the cells in the Trim column, then hit CTRL+C to copy the. Data doesn’t always import or paste into Excel as nicely as you want. There are several ways to remove spaces and clean up data in Excel.

Remove space in time cell