How to remove space between columns in excel
3 quick ways to remove extra spaces between words or delete all spaces from Excel cells. You can use trim formula, Excel Find & replace or special Excel add-in to clean up cells' content. Show
When you paste data from an external source to an Excel spreadsheet (plain text reports, numbers from web pages, etc.), you are likely to get extra spaces along with important data. There can be leading and trailing spaces, several blanks between words and thousand separators for numbers. Consequently, your table looks disorderly and becomes difficult to use. It may be a challenge to find a customer in the Name column since you search for "John Doe" which has no excess spaces between the names while the way it looks in your table is "John Doe". Or numbers can't be summed up, and again extra blanks are the ones to blame. In this article you'll find how to clean up your data. Trim blanks between words to 1, remove trailing / leading spacesFor example, you have a table with 2 columns. In the column Name, the first cell contains "John Doe" written correctly without excess spaces. All other cells have extra blanks between the first and the last names. At the same time these cells have irrelevant blanks before and after the full names known as leading and trailing spaces. The second column is called Length and shows the number of symbols in each name: Use the Trim formula to remove extra spacesExcel has the Trim formula to use for deleting extra spaces from text. Below you can find the steps showing how to use this option:
Using Find & Replace to remove extra spaces between wordsThis option needs fewer steps, but allows only deleting excess spaces between words. Leading and trailing spaces will also be trimmed to 1, but will not be removed.
3 clicks to neat data with Trim Spaces toolIf you often import data to Excel from external sources and spend much time polishing up your tables, check out our Text tools for Excel. The Trim Spaces add-in will clean data imported from the web or any other external source. It removes leading and trailing spaces, excess blanks between words, non-breaking spaces, line breaks, non-printing symbols and other unwanted characters. Also, there is an option to convert words to UPPER, lower or Proper Case. And if you need to change text numbers back to the number format and delete apostrophes, this will not be a problem either. To remove all extra spaces in your worksheet, including excess paces between words, this is what you need to do:
Isn't it faster than with the two previous tips? If you always deal with data processing, this tool will save you hours of precious time. Remove all spaces between numbersSuppose, you have a workbook with numbers where the digits (thousands, millions, billions) are separated with spaces. Thus Excel sees numbers as text and no math operation can be performed. The easiest way to get rid of excess spaces is using the standard Excel Find & Replace option:
Using formula to remove all spacesYou may need to delete all blanks, like in a formula chain. To do this, you can create a helper column and enter the formula: Here A1 is the first cell of the column with numbers or words where all spaces must be deleted. Then follow the steps from the part using formula to remove extra spaces between words to 1 Video: how to remove spaces in ExcelYou may also be interested inHow do I remove spaces between two columns in Excel?Remove all spaces between numbers. Press Ctrl + Space to select all cells in a column.. Press Ctrl + H to open the "Find & Replace" dialog box.. Press Space bar in the Find What field and make sure the "Replace with" field is empty.. Click on the "Replace all" button, and then press Ok. Voila! All spaces are removed.. How do I reduce column space in Excel?Set a column to a specific width. Select the column or columns that you want to change.. On the Home tab, in the Cells group, click Format.. Under Cell Size, click Column Width.. In the Column width box, type the value that you want.. Click OK.. |