How to remove spaces in Excel

In Excel spreadsheets, sometimes the data has extra spaces in the words, or spaces between numbers. In order for the data in the spreadsheet to be more standard and beautiful, you need to remove unnecessary spaces from the data. If you do not know how to delete spaces in Excel, please refer to the following article.

How to remove spaces in Excel Picture 1

The article guides how to remove spaces in Excel, please follow along.

REMOVE WHITE BETWEEN THE WORDS

Use the TRIM function to remove spaces

The function of the TRIM function is to remove all spaces from a text, leaving only a space between words.

Syntax of the TRIM function: = TRIM (text)

For example, you need to remove spaces between words in the First and Last name column as below.

How to remove spaces in Excel Picture 2

First you need to create an extra column. Enter the formula in the first cell in the secondary column   = TRIM (B7) with B7 being the cell to remove spaces.

How to remove spaces in Excel Picture 3

So you deleted the space between the words in the first cell, you copy the formula down to the next cells to remove the space of all columns.

How to remove spaces in Excel Picture 4

Now you can copy the sub column data to the main column by selecting the secondary column and pressing Ctrl + C to copy, then put the mouse in the first cell of the main column and right-click and select Value (V) in Paste Options.

How to remove spaces in Excel Picture 5

So you can delete the extra column.

How to remove spaces in Excel Picture 6

So using the TRIM function, you remove the spaces between words and leading spaces. But with big data, you use the TRIM function will be very time consuming.

Use Find & Replace

Using the search and replace feature ( Find & Replace ) will help you remove spaces between words faster, but leading spaces will still have a space that cannot be removed.

Step 1 : Select the data area to delete spaces, next you select Ctrl + H to open the Find & Replace dialog box .

How to remove spaces in Excel Picture 7

Step 2: In the Replace tab, press the spacebar twice in the Find what box , in the Replace With box press the space key once (searching for locations with two spaces will replace them with a space). Then click Replace All to replace all, a message appears, then click OK .

How to remove spaces in Excel Picture 8

Because there are many spaces with more spaces than the two spaces, click Replace All to replace, when the message We couldn't find anything to replace appears , click OK and turn off the Find & Replace dialog box. .

How to remove spaces in Excel Picture 9

So all spaces will be removed leaving only one space between words.

How to remove spaces in Excel Picture 10

DELETE WHITE PLACES BETWEEN NUMBERS

Use the Substitute function

The TRIM function helps you remove spaces between words and keep between words 1 space, but if you want to remove all spaces between numbers without any spaces, you need to use the SUBSTITUTE function .

You just need to use the SUBSTITUTE function formula to remove spaces.

= SUBSTITUTE (C7; ''; '')

Where C7 is the cell containing the number to delete all the spaces.

How to remove spaces in Excel Picture 11

Copying the formula to the remaining cells will delete all spaces between the numbers.

How to remove spaces in Excel Picture 12

Use Find & Replace

Step 1: In Excel worksheet, select the numeric data area to remove spaces, then press Ctrl + H to open the Find & Replace dialog box .

How to remove spaces in Excel Picture 13

Step 2 : In the Find & Replace dialog box, press the spacebar once in the Find What box and in the Replace with box you do not enter anything. Click Replace All to remove the space between numbers.

How to remove spaces in Excel Picture 14

If there are spaces in the middle of the numbers, then click Replace All again until no spaces are found, turn off Find & Replace . All spaces you select will have their spaces removed.

How to remove spaces in Excel Picture 15

Above are ways to help you can delete spaces between words, remove spaces between numbers in Excel. Hopefully the article will help you in working with Excel. Good luck!