When you are comparing two columns for duplicates that you know are there, but your formulas cannot find a single duplicate entry. Or, when you are adding up two columns of numbers, but keep getting only zeros. When your obviously correct VLOOKUP formula returns just a bunch of N/A errors. These are only some examples of the issues you'll be searching for a solution to. And the whole thing is caused by greater space hidden before, after, or among the numbers and text values in the cell. Fortunately, the numerous functions and capabilities of Microsoft Excel help you to manage information efficiently and smartly.

Types of Extra Spaces in Excel

• Leading spaces

"Leading spaces" refer to the empty gaps at the beginning of content within a cell.

• Trailing spaces

"Trailing spaces" are the empty gaps found at the end of content within a cell.

• Added in-between spaces

In the context of Excel, "in-between spaces" point to those unintentional spaces between the cell contents. These seemingly minor gaps might seem harmless but can throw off the functioning of functions, leading to inaccurate results. To maintain accuracy and reliability, it's a good practice to carefully scan for and remove these gaps whenever encountered.

• Non-breaking spaces

The Non-breaking spaces in Excel can lead to a host of problems. Unintentionally missing spaces where they're needed to separate text can hinder the intended functionality of the functions you're working with.

How to Remove Spaces after Text in Excel

This section will teach you how to remove spaces before or after text in Excel. You will learn two different methods to remove spaces in Excel before/after texts: the TRIM function and the Find and Replace option.

Using Trim Function

The TRIM function in Excel is used to remove any extra spaces before, after, or within a text value in a cell. It can help you clean up your data and make it more consistent and accurate.

The syntax of the TRIM function is:

=TRIM(text)

where text is the cell or text value that you want to trim. You can also use a reference to another cell that contains the text value.

Example- Using TRIM Function to remove spaces in Excel

Let’s see how to use the TRIM function in Excel to remove spaces before or after numbers or text in a sample dataset. Here are the steps to follow:

The leading spaces can be observed by making the text right-aligned.

Step 1: Click on an empty cell and type an equal sign (=) to access the functions in Excel. Then type “trim” and select the “TRIM” option from the list below or press Tab on your keyboard.

Step 2: Select the cell that contains the text with spaces that you want to trim. You can also type the cell reference or a text value in quotation marks.

Step 3: Make sure to close the parentheses and then press Enter. The result will be displayed without any spaces before or after the text.

The TRIM function is a quick and efficient way to remove trailing spaces or remove spaces between words in Excel from text and make your data more consistent and accurate.

Using the Find & Replace option

Another method to remove spaces after text in Excel is to use the Find and Replace option. Here are the steps to follow:

Step 1: Select the range of cells that contains the text with spaces that you want to remove.

Step 2: Go to the Home tab on the ribbon and click on Find & Select in the Editing group. Then choose Replace from the drop-down menu. The Find and Replace dialog box will show up.

Alternatively, users can also use the shortcut key “CTRL + H” to access the Find and Replace dialog box.

Step 3: Enter a space in the "Find what" box.

Step 4: In the “Replace with” box, leave it blank. This means that you want to replace the spaces with nothing.

Step 5: Click on “Replace All” to remove all the trailing spaces in the selected range of cells. You can also click on Replace to remove one space at a time. You will see a message telling you how many replacements were made.

Method of Removing only the Spaces in Excel before Text/Numbers

To remove spaces before text in Excel, users can take advantage of the TRIM function in a similar way as before. TRIM Function allows users to remove leading spaces from the text in Excel efficiently. Let’s look at the previous example to understand how the TRIM function works with leading spaces:

Step 1: We can observe the leading spaces when the cells are left aligned. To remove these, we will enter the TRIM Function in cell B2.

Step 2: Choose the cell with leading spaces. In our example, we will select A2.

Step 3: Hit the Enter key for TRIM Function results. To populate the remaining cells, simply use the “Autofill” handle.

Remove Spaces More Easily——WPS Office

WPS Office has been meticulously designed to simplify and elevate your daily tasks. This dynamic office suite encompasses a comprehensive array of essential productivity tools. What sets it apart is its integration of additional functionalities, including a user-friendly PDF editor and an AI-powered tool seamlessly harmonized with the suite. These extra features seamlessly enhance the existing Microsoft Office productivity tools.

Moreover, WPS Office prioritizes user-friendliness, making it the ultimate solution for creating documents, handling spreadsheets, and crafting compelling presentations.

Pros

• Cost-free

• Compatibility across platforms

• Intuitive user interface

• Simplified usage

• Exceptional customer support

  
  

  Trustpilot4.8

  WPS Office- Free All-in-One Office Suite

  Free Download

  5,820,008 User

  

  Algirdas Jasaitis

  logo

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

FAQs

Q1. Why isn't TRIM working in Excel?

One possible reason why TRIM isn't working in Excel is that the spaces you're attempting to remove are non-breaking spaces rather than regular spaces. Non-breaking spaces are a type of space character that prevents the occurrence of a line break at that point. They are frequently used in web pages to control text layout. Non-breaking spaces are distinguished from regular spaces by their ASCII code, and the TRIM function can only remove spaces with an ASCII code of 32. Non-breaking spaces have the ASCII code 160. You can use the SUBSTITUTE function or the Find and Replace option to remove non-breaking spaces.

Q2. What is Len and TRIM in Excel?

In Excel, the LEN function estimates the number of characters in a cell, which aids in locating cells with specified character counts, especially when cleaning data. The TRIM function removes unnecessary spaces from text, making it a convenient tool for cleaning up imported data or fixing spacing issues within cells. You may efficiently clean and prepare your data for analysis or display by merging these tools, assuring correctness and uniformity in your Excel spreadsheets.

Mastering Space Management in Excel with Confidence

This article has equipped you with knowledge about the potential caused by spaces that can disrupt your Excel functions. To enhance your skills, our guide assists on how to remove spaces after text in Excel while providing insights. WPS Office is an excellent platform to learn and practice these techniques. If you are still unfamiliar with its potential consider downloading WPS Office now.