HomeApplicationOffice information

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

  • How to use the SUM function to calculate totals in ExcelHow to use the SUM function to calculate totals in Excel
    sum is a popular and very useful excel function, and is also a basic arithmetic function. as its name suggests, the sum function is used to calculate totals in excel. and the parameters can be single parameters or ranges of cells. in this article, tipsmake.com will guide you to use the sum function to calculate the sum in excel, the common errors when calculating sum by sum and how to fix it.
  • Normalize string when pressing Enter key in ExcelNormalize string when pressing Enter key in Excel
    the following article will guide in detail how to use functions to normalize strings when pressing the enter key in excel. this function will remove spaces at the beginning and end of the line, delete two consecutive white space characters between words.
  • How to count characters in ExcelHow to count characters in Excel
    to count characters in excel we need the help of the len function. the len function helps you count the number of characters including spaces in a given phrase. normally, the len function is not used alone, it will combine with other functions based on certain purposes.
  • How to Remove spaces and carriage returns in WordHow to Remove spaces and carriage returns in Word
    knowing the tips of removing spaces as well as newlines in word in the latest versions is a great advantage for you, because in the text editing process on this tool, there will be times when spaces appear. and many errors, of which the automatic newline error is an example.
  • How to use COUNTIF function on ExcelHow to use COUNTIF function on Excel
    the countif function on excel is the cell count function to satisfy the given condition, often used in statistics tables.
  • How to use the MOD function and QUOTIENT function in ExcelHow to use the MOD function and QUOTIENT function in Excel
    injury in excel has many ways of doing it, can be used manually or using the calculation function.
  • 8 little-known Excel functions that can save you a lot of work8 little-known Excel functions that can save you a lot of work
    even seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
  • How to use MID functions to get strings in ExcelHow to use MID functions to get strings in Excel
    mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
  • How to use Excel's VALUE functionHow to use Excel's VALUE function
    excel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
  • How to use the WRAPROWS function in ExcelHow to use the WRAPROWS function in Excel
    having trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.

Office information