WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to remove spaces after text in Excel

September 7, 2023
6.8K Views

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.

Tips for detecting spaces:

  • Use the LEN function to count the number of characters in a cell, including spaces, and compare it to the expected length of the value.

  • Try changing the alignment of the cells to see if they line up properly. If there are trailing spaces, the cells will not align correctly when you use right or center alignment.

logo

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.

Excel right alignment in cells


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.

Excel functions


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.

Excel Trim Function


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.

Excel Trim Function results


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.

Excel spaces in text


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.

Excel find and replace option


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.

Excel Find what


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

Excel replace with


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.

Excel find and Replace to remove spaces in text


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:

Excel 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.

Excel Trim function


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

Excel Trim function select cell


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

Excel Trim function results


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.

WPS Office


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


    Trustpilotstars4.8
    WPS Office- Free All-in-One Office Suite
    5,820,008 User
    avator
    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.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.