HomeApplicationOffice informationHow to use the function to delete spaces in Excel

How to use the function to delete spaces in Excel

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
SUBSTITUTE excel function

You should read it

May be interested

  • Guide to transfer ebook into the Amazon KindlePhoto of Guide to transfer ebook into the Amazon Kindle
    documents brought into kindle are stored in online personal documents (kindle personal document online). when buying a new kindle, documents on the old kindle will automatically be returned to the new machine. users can also access personal documents from kindle applications on other platforms.
  • How to fix the error of accent marks when writing capital on WordPhoto of How to fix the error of accent marks when writing capital on Word
    many word capitals encounter misleading errors, which are not in the correct alphabetical position and often occur with fonts tcvn3, vni-windowns, ..
  • How to create duplicate data entry notifications on ExcelPhoto of How to create duplicate data entry notifications on Excel
    data validation on excel is a feature that helps create notifications when users enter data incorrectly with established rules. so how to create duplicate data message on excel?
  • How to convert Cad to Word, PDF, ExcelPhoto of How to convert Cad to Word, PDF, Excel
    after designing cad drawings, you want to transfer cad to word, excel to print, present ... or you want to transfer cad to pdf so that any computer can read this file but don't know how to do it what? don't worry, the following article will guide you to transfer cad to word, pdf, excel very simply but not everyone knows it!
  • How to create SmartArt graphics in ExcelPhoto of How to create SmartArt graphics in Excel
    smartart on excel will help users to display content, data instead of using tables or just using text.
  • How to use the Power function in ExcelPhoto of How to use the Power function in Excel
    the power function in excel is a power function with a given number that you don't need to calculate manually.

Office information