Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Removing Spaces

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Removing Spaces.

Do you have a lot of data that contains spaces, and you need to remove those spaces? Perhaps you imported it from another program, or the spaces were entered by mistake. For example, you may have a large number of policy numbers in a worksheet, and there are spaces in the policy numbers. If you want to remove those spaces, there are two approaches you can use.

The first approach is to use the SUBSTITUTE function. Let's say that a policy number is in cell A5. In cell B5 you could use this formula:

=SUBSTITUTE(A5," ","")

The result is that cell B5 contains the policy number with all the spaces removed.

The second approach works well if you have a lot of cells containing spaces, and you want to remove them in one step. Create the following macro:

Sub NoSpaces()
    Dim c As Range

    For Each c In Selection.Cells
        c = Replace(c, " ", "")
    Next
End Sub

Select the cells you want to modify, and then run the macro. It examines each cell in the selected range, removing any spaces in that range. The result is then placed back in the same cell.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3037) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Removing Spaces.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Andres    18 Jun 2016, 05:43
hello Fergie, I think the following macro will help you to get what you want.

Sub CleanSpaces()
    Dim Place, WorkRange
    Place = Selection.Address
    Dim a As Range
    Set WorkRange = Selection
            For Each a In WorkRange.Areas
                a.Formula = Application.Trim(a.Formula)
            Next a
    Range(Place).Select
End Sub
Fergie    13 Jun 2016, 18:22
This seems to have limited use. If I understand what I am reading, the macro would render this text as:
"Thisseemstohavelimiteduse.IfIunderstandwhatIamreading,themacrowouldrenderthistextas:"

Is there a way to delete only the spaces between paragraphs in a cell?

For example, when you pull a csv into Excel that contains scan results from a tool, say AppDetective. Some cells in that resulting spread sheet will have several paragraphs, and several empty "lines" between each paragraph.

How to we get rid of those spaces but leave the text readable?
Mansoor Pirwa    16 Nov 2014, 21:45
I used find and replace command on selected cell. In find just one space and replace left blank
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.