How to use the function to delete spaces in Excel

Deleting white space with functions in Excel makes it easier for users to handle content, instead of traditional editing.

When processing Excel documents, these documents do not always have to be formatted in standard content and tables that users need to edit. For example, the word redundant between the beginning and the end, or between words together. Of course, if the content is short and easy to control, we can do it manually, delete it manually. If Excel content is long, you can use the Excel space removal function.

In Excel, there are two functions to delete spaces that are TRIM functions and SUBSTITUTE functions. These two functions will be used to delete different whitespace, and be guided by Network Administrator in the article below.

  1. These are the most basic functions in Excel that you need to understand
  2. How to use ConcateNate function on Excel
  3. How to use AVERAGEIF function in Excel

Instructions for deleting whitespace in Excel

We will take the example with the following Excel content with a space name and use the above two functions to delete.

How to use the function to delete spaces in Excel Picture 1

Step 1:

The TRIM function will be used to clear all spaces before and behind the data, leaving only the space between the spaces in the middle.

In the text input box enter the formula = TRIM (B19) and press Enter.

How to use the function to delete spaces in Excel Picture 2

Step 2:

Immediately the content will be omitted redundant whitespace and leave the correct content white space.

How to use the function to delete spaces in Excel Picture 3

Step 3:

The SUBSTITUTE function is a string replacement function and can be used to clear all spaces in the content.

Enter in the new content field the formula = SUBSTITUTE (B19, "", "") and then press Enter.

How to use the function to delete spaces in Excel Picture 4

As a result, we will have no white space as shown below.

How to use the function to delete spaces in Excel Picture 5

Depending on the content required in Excel, we choose to use the TRIM function or SUBSTITUTE function accordingly. This way is faster to delete the space in the content, without clicking and pressing the Backspace key.

See more:

  1. How to combine 2 columns Full name in Excel does not lose content
  2. How to use SUMPRODUCT function in Excel
  3. How to combine Sumif and Vlookup functions in Excel

I wish you all success!

4 ★ | 1 Vote | 👨 170 Views
« PREV POST
NEXT POST »