Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Removing Spaces.

Removing Spaces

by Allen Wyatt
(last updated February 22, 2020)

27

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.

One final note: You may wonder about using the TRIM function. This worksheet function is helpful in removing excess spaces, but it cannot be used to completely remove spaces, as described in this tip.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10741) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Removing Spaces.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adding a Break to Your Document

Want to modify the way your text flows between pages in a document? Word allows you to insert several types of breaks ...

Discover More

Automatically Identifying Repeated Words

Need to find out how many times words are repeated in a document? If so, you'll appreciate the discussion in this tip ...

Discover More

Specifying the Y Value in X of Y Page Numbering

Page numbers in Excel headers and footers have always been a source of frustration for users. This tip recounts one ...

Discover More

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!

More ExcelTips (ribbon)

Extracting Numbers within a Range

If you have a large number of values in a column, you may want to move the values that meet specific criteria to another ...

Discover More

Automatically Adding 20% to an Entry

When you are developing a worksheet for others to use, you may want to have entries in a particular cell (or cells) be ...

Discover More

Jumping to a Range

Need a quick way to jump to a particular part of your worksheet? You can do it by using the Go To dialog box.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 6?

2020-03-06 02:45:38

Alan Elston

Glad you are better , Peter, and happy that it was not the coronavirus!!! , Alan


2020-03-05 05:54:54

Peter Atherton

The following works in Excel 2007.

Sub RemoveCR()
Dim rng As Range, c As Range, col As Integer
Set rng = Selection
col = rng.Column

For Each c In rng
c = Evaluate("SUBSTITUTE(" & c.Address & ", char(10), """")")
Next
With rng
.Columns(col).AutoFit
.RowHeight = 15
End With
End Sub

Sorry it took so long, I've had 'flu.


2020-02-29 09:19:41

Peter Atherton

Alan,
Thanks for your insight. Yes I have 2016 and this is what I tested it on. I also have 2007 on my pc and tested the code on in that and sure enough it copied the results down.
I guess, in future, we'll have to say what version this is for.

Thanks again and apologies to Nigel for getting a bit shirty.


2020-02-28 11:01:27

Alan Elston

Or another way to get my Email address…

Put this in a cell
d o c . a e l s t e i n @ t - o n l i n e . d e
and then select that cell

Now use Allen Wyatt’s macro on it !!

Or if you prefer, try this alternative

Sub GetAlanElstonsEmail()
Selection = Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", "" "", """"))")
End Sub

Or another way to get my Email address…

Put this in a cell
d o c . a e l s t e i n @ t - o n l i n e . d e
and then select that cell

Now use Allen Wyatt’s macro on it !!

Or if you prefer, try this alternative, which will also get rid of any vbLf ( Char(10), Chr(10) ) you might have in the cell

Sub GetAlanElstonsEmail()
Selection = Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", "" "", """"))")
Selection = Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", char(10), """"))")
End Sub


2020-02-28 10:51:49

Alan Elston

***EDIT:-

I should have said this
Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", char(10), """"))")
In place of this
Evaluate("SUBSTITUTE(" & Selection.Address & ", char(10), """")")
( the lack of an edit function here is unfortunate :) )
Alan

P.S. Anyone is welcome to contact me by Email and pass a file to try out.
( I un checked the box to hide my Email , so if you click on my name to the left , then you should get my email. ( Alternatively, depending on what browser you are using , if you right click on my name, you may get the option to copy the Email address hidden behind it. ) )


2020-02-28 10:25:05

Alan Elston

Peter and Nigel
My experience is not extensive of what versions react in what way since Microsoft made the changes around 2016 which effected how our “ Evaluate Range” one liners “ behave.
But…..
I will take a guess that Peter , you have Office 2016 or higher.
I will further take the guess that Excel 2013, ( which Nigel you have ), is possibly working similar to earlier versions .

If that is the case.
_ 1) I would most likely expect that Peter, your macro adaptations would work in your Excel .
_ 2) I would expect the result that Nigel , you are getting.

_.____

Nobody yet has fully been able to understand how our “ Evaluate Range” one liners “ work. People like Rick Rothstein and then Willy have taken some interest and come up with some very neat one liner coding to replace looping.
Somehow it appears that this technique seems to tap into the way an Excel worksheet does its “along the columns then down the row “ screen updating, which it does routinely for the whole worksheet even when only one cell modification is done.
In the spreadsheet we can take advantage of this by “exposing” the extra unseen work that is done using the famous CSE entry, which can allow you to get array type calculations.

Willy once described these “ Evaluate Range” one liners “ as a sort of “hidden array function”

Unfortunately , although the information for array calculations is available to us , it does not always appear to be seen.

At least that was the case…..
Now, the new “array spilling” introduced by Microsoft does away with the need for doing the CSE
A by - product of this, is that our “ Evaluate Range” one liners “ work a bit differently and more often than not return us the array of values that we are looking for. I expect that is happening by you, Peter.
Although “array spilling” has only recently been introduced, it is obvious to me from what I mentioned before from inconsistencies reported in forums from around 2016, that Microsoft were already making the changes that finally made the array spilling possible. They introduced various changes sometimes through updates,( which as often, cause a chaos in incompatibilities, and god knows what other bugs )


Previously our “ Evaluate Range” one liners “ needed a trick or two to “coerce” them into giving us the array information that they had to offer. A typical trick is to use some extra thing in the calculation that does generally give an array back to us. Somehow then , internally the calculation makes use of the array information that is there. The final result , in array form is returned to us.

_._______

One of the simplest tricks we used to do, ( and in newer Excel version seem to be less likely to need to do ) is simply to do like

If({1},xxxxxxxxxxxxxxxxx)

Where

xxxxxxxxxxxxxxxxx

is the thing which we are expecting has an array of values to offer us , but is not in earlier Excel versions giving us.

_.________

So…..
I think if Nigel, you try this

Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", char(10), """"))")

in place of this

Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", "" "", """"))")

then you may get the same results as Peter……… possibly.


Alan Elston


2020-02-28 08:17:46

Nigel Gale

Hi Peter, I have no reason to doubt you and appreciate your input. I am using Excel Version 2013 and applied your code as advised. The code test only related to the Col N range ("N1:N7") with N7 being the last cell I populated. When I ran the macro it removes the Alt + Ent lines but replicates the contents of cell N1 data into cells N2-N7 overwriting the original contents in those cells. Happy to send you the file if you give me a mail address to send it to. I appreciated your help thanks


2020-02-28 03:38:56

Alan Elston

@Peter.
Since approximately 2016 Microsoft have been changing things in how Excel works in preparation for the new array spilling. I have seen forum posts where the behaviour of our “ “ Evaluate Range” one liners “ behave differently now in different Excel versions.
I don’t know if that may explain the discrepancies in this case. Your adaptations work fine by me, but I only have versions to Excel 2010.
My best guess so far , based on what has been reported is that the changes would not prevent your adaptations working. But I cannot be sure, - I have not yet gained enough experience with the issue. ... I have seen it go both ways in reported problems: –... some things work that we could not get easily to work before, ...and other things that worked fine , no longer work in some newer Excel versions as they do still in earlier versions,.

At any rate these changes , in my opinion, are another case of Microsoft making changes that cause more problems than the improvements are worth,. Just my opinion.

Alan Elston

( P.S. It seems that Allen Wyatt’s Comment’s Image/Figures is currently broken. I often use a imgur com link alternative, but , of course, if you do that you will have to live with the mangled text after it comes back from the spam checker … those “.. new line / carriage return “ inconsistencies .. !!!! :) )


2020-02-27 08:06:44

Peter Atherton

Well you will just have to believe me


2020-02-27 08:05:43

Peter Atherton

Nigel Are you sure? The data in column C is copied from column A before running the macro. The data is the same apart from the lack of lines.

[{fig}]


2020-02-27 08:03:25

Peter Atherton

Nigel Are you sure? The data in column C is copied from column A before running the macro. The data is the same apart from the lack of lines.

[{fig}]


2020-02-26 12:18:16

Nigel Gale

Hi Alan, Thanks for your input. I have tried both of your suggested macro codes and they both do exactly what I was looking for. Many thanks to everyone for their input.


2020-02-26 08:12:10

Nigel Gale

Peter, I appreciate your help. I have replicated the code as written and placed The Workbook (Before Save) in ThisWorkbook and RemoveCF() in Module 1
It does remove any lines created by Alt + Enter but its actually replacing all the Col N cells N1-N7 ( the selection ) with the text shown in Cell N1 - i.e. all the notes in cells Col N1-N7 are overwritten with the same text as in Cell N1.

Thanks


2020-02-26 07:41:51

Alan Elston

Hello again Nigel……… Here is that image again: …..( It did not seem to upload properly ) ….. ….. https://imgur.com/Zr4C3Tq ….. If you still require more detailed help, then I might suggest you contact me directly per Email or at w w w . e x c e l f o x . c o m ….( without the spaces, Lol!! ) … it may be more appropriate and easier to pass larger amounts of information there Alan Elston


2020-02-26 07:32:03

Alan Elston

Hello Nigel.
I copied

Received From Site B -Monday, Sent To Site C - Tuesday, Received Back From Site C - Wednesday

and copied it into a couple of cells N6 and N7
Then, I selected one of the cells , N7 , and did the Alt + Enter which you mentioned ( I can’t remember having ever done that before myself)
It does not do anything for me if I do that
However , if I select somewhere in the text in the formula bar, and do the same, then the text is divided. I expect this is what you are referring to.

I then used this short macro below to call the function which is at one of the site references that I gave in my post of 2020-02-23 06:01:12


Sub TestWtchaGot ()
Call WtchaGot (Range("N6").Value)
Call WtchaGot (Range("N7").Value)
End Sub

The function , WtchaGot , analyses the string in different ways.
In the image below I have shown part of the output from running that test macro above

As you can possibly see , the cell which had the Alt+Enter applied is indicating an extra character,
vbLf

I think I have also sometimes read elsewhere that , conventionally, Excel uses a vbLf as the line separator for a cell

So the thing you need to be using in a macro to be filtered out is the
vbLf
Alternatively you can use this, which in VBA is seen in coding as the same thing
Chr(10)

Alan Elston

( P.S. Just to add to the confusion, Excel will usually recognise either vbCr , vbLf or vbCr & vbLf as the row separator when receiving a text string to be pasted into a worksheet from the clipboard.
More usually vbCr & vbLf is used, but not always.
This should not cause you any problems as long as you consider each cell individually.
My testing suggest that both the macro from Allen Wyatt and that of the Willy’s adaption from Peter both work the same.
Willy’s adaptation from Peter is generally faster
However, because we are dealing with those things that often unexpectedly cause problems when copying and pasting, I would, in this case, go for Allen Wyatt’s macro, since it may be easier to detect and overcome any strange unforeseen problems which you might get. )

_._________________________

So finally, my best guess is that this will do best what you want

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
Let c.Value = Replace(c.Value, vbLf, "")
Next
End Sub

or

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
Let c.Value = Replace(c.Value, Chr(10), "")
Next
End Sub


[{fig}]


2020-02-25 19:06:50

Nelson

Replace is another easy method. CTRL+F, Replace tab. Press Space bar in the Find What. Leave Replace with blank.


2020-02-25 14:54:57

Peter Atherton

Nigel
Oops! I changed the lastrow co;umn to N but forgot to change the Range in the Before_save macro. change to:

Set rg = Range("N1:N" & LRow)


2020-02-25 14:46:27

Peter Atherton

Nigel
I had outlined the steps to take when my post was mangled. Anyway here is the code that goes in the Workbook module (Click the Excel icon at the bottom of the last sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rg As Range, LRow As Long
'Change sheetname to suit
Sheets("Sheet11").Select
LRow = Cells(Rows.Count, "N").End(xlUp).Row
Set rg = Range("A1:A" & LRow)
rg.Select
RemoveCF
End Sub

The other code has not changed but is is shown again to keep it all together. It should be copied into an ordinary module. It ws tested with Alt+ Enter.

Sub RemoveCF()
Dim rng As Range, col As Integer
Set rng = Selection
col = rng.Column
Selection = Evaluate("SUBSTITUTE(" & Selection.Address & ", char(10), """")")
Columns(col).AutoFit
rng.RowHeight = 15
End Sub

If you do not want to change the row height and autofit just comment them out.


2020-02-25 04:47:41

Nigel Gale

Thanks To everyone for the replies. I was probably not specific enough in my description so apologise for that.

Within an Excel cell (containing a long string of text) i.e. Col N contains cells that show a history of updates for the row of data.
e.g. Cell N4 = Received From Site B -Monday, Sent To Site C - Tuesday, Received Back From Site C - Wednesday

Some Users are using Alt + Enter within that cell to separate that text into lines (within the cell) which makes it more readable & displays in the cell as
Received From Site x -Monday,
Sent To Site y - Tuesday,
Received Back From Site y - Wednesday

It is the Alt + Enter lines within a cell that I need to search and remove (recreating a single line of text) with macro code before saving to a .txt file
Thanks


2020-02-24 09:17:40

Alan Elston

Just for clarity, here below is my post again from yesterday, 2020-02-23 06:01:12

It is almost identical. The only difference is that I have not included the 2 links this time. Because of this omission, I expect that this will post direct and not go via a detour to be checked. For spam.
Therefore the “carriage returns / line feeds “ will not get messed up, and you will see it as I posted it

_._________________________________________

Hello Nigel.
A problem with these sot of things is that there is inconstancy in what anyone means by a “carriage return”
You might know that early in computers in a text string , two “invisible” characters were included to force the printer to make a new line
One typically was called something like
“carriage return”
, to make the printing arm go back to the left side, and the other was called something like
“line feed”
, to make the printer notch up a new line
These two things have been kept for compatibility reasons. So we have three possibilities that we have to define a ……new line…. , for want of a better word..
Some systems react to either one or the other or both together in different ways.
You would probably need to experiment with 3 modifications to Allen Wyatt’s coding, trying these alternative code line versions:
c = Replace(c, vbCr, "")
c = Replace(c, vbLf, "")
c = Replace(c, vbCr & vbLf, "")

I am not sure if there would be an alternative form of Wiily’s alternative.

Sometimes there are more complicated issues related to the … “linefeeds….”.. . when you are play around with text files and excel files so you might need to look further ………………….


2020-02-24 09:02:34

Alan Elston

I see that Peter has shown how to use Willy’s code. ( I couldn’t understand why I kept getting error when I tried: I see now that in Excel you use Char( ) - in VBA it is Chr( ) , so that difference was catching me out )

Note:
Char(10) or in VBA Chr(10) is equivalent to vbLf
Char(13) or in VBA Chr(13) is equivalent to vbCr

In VBA you also see sometimes
vbCrLf
or
vbNewLine
Those are usually interpreted as the two characters
vbCr & vbLf
or
Chr(13) & Chr(10)

If you try Peter’s version of Willy’s code line, then you might want to try the three things, as that may give you different results

The inconsistency with how these things are used and interpreted cause a lot of problems.
You see that even here sometimes , for example – when our posts are sent off to be checked by Allen Wyatt for spam .. when they come back the line format is sometimes lost – the post comes back in a form that is interpreted here sometimes as a long single text string.
Actually this page has been going scatty as I tried to post, so its possible that our posting of the symbols has been causing problems.

Here again is my suggested versions of Allen Wyatt’s code line to try.
They may come out clearer if this posts directly rather than taking the detour to be spam checked….


c = Replace(c, vbCr, "")
c = Replace(c, vbLf, "")
c = Replace(c, vbCr & vbLf, "")

These next are just a different way of writing the same code lines
c = Replace(c, Chr(13), "")
c = Replace(c, Chr(10), "")
c = Replace(c, Chr(13) & Chr(10), "")


2020-02-23 19:28:40

Peter Atherton

Nigel
This is just a version of Willy VanHealan's code. Char(10) is the carriage return code. To get is to run before closing you need to create a Before_Close macro. The macro should activate the sheet with the data and select the range and then, call the Macro

Sub removeCF()
Dim rng As Range, col As Integer
Set rng = Selection
col = rng.Column
Selection = Evaluate("SUBSTITUTE(" & Selection.Address & ", char(10), """")")
Columns(col).AutoFit
rng.RowHeight = 15
End Sub

HTH


2020-02-23 16:05:26

Peter Atherton

Nigel, try the following:Sub removeCF()Dim rng As Range, col As IntegerSet rng = Selectioncol = rng.ColumnSelection = Evaluate("SUBSTITUTE(" & Selection.Address & ", char(10), """")")Columns(col).AutoFitrng.RowHeight = 15End SubNotes: Evaluate must work on the active sheet (uness it i referenced in the code)You should call it from the Workbook module in a Before Save Event.The Before_Save macro should activate the sheet and then select the range to operate on.Optional:If you do not want to resize the column you can delete this line and the reference on the DimThe row height is the standard height of a row you might want to change this or remove it.


2020-02-23 06:01:12

Alan Elston

Hello NigelA problem with these sot of things is that there is inconstancy in what anyone means by a “carriage return”You might know that early in computers in a text string , two “invisible” characters were included to force the printer to make a new lineOne typically was called something like “carriage return” , ... that was to make the printing arm go back to the left side, ... and the other was called something like“line feed”, ... that was to make the printer notch up a new lineThese two things have been kept for compatibility reasons. So we have three possibilities that we have to define a ……"new line"…. , for want of a better word.. Some systems react to either one or the other or both together in different ways. You would probably need to experiment with 3 modifications to Allen Wyatt’s coding, trying these alternative code line versions:c = Replace(c, vbCr, "")c = Replace(c, vbLf, "")c = Replace(c, vbCr & vbLf, "")I am not sure if there would be an alternative form of Wiily’s alternative. Sometimes there are more complicated issues related to the … “...linefeeds….”.. . when you are play around with text files and excel cells and worksheets, ... so you might need to look further, for examplehttp://www.eileenslounge.com/viewtopic.php?f=18&t=33834&sid=5a294cad385ca09b43a9b89d65da0eb3http://www.excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-stringAlan Elston


2020-02-23 03:58:43

Nigel Gale

Hi Alan, Really enjoy your Tips and Nuggets. Todays tip about spaces reminds me of an issue I have. My question is what function would be best to remove carriage returns in a cell.

I built and administer a large macro based National Excel Database, where 62 sites across the UK update their data via .txt files. The data is then pulled into a master spreadsheet for weekly analysis. Very occasionally a user will enter a carriage return in the Column N notes field, to make their notes more readable (than a single string), which results in the loss of all data shown below that cell, when saved.

Because .txt file thinks in lines of data, entering a carriage return in Col N notes forces the data in the next Col A cell to move down (effectively inserting a new line). This fools the .txt file into thinking there is no data held below and ignores it - resulting in the loss of data.

I want to incorporate a search of all the cells to remove any carriage returns prior to Save.

Thanks


Nigel


2020-02-22 14:09:40

Bill Daily

I always select the area, then select find/replace - put a space in the find and nothing in the replace.
Time wise I find it to be the fastest option


2020-02-22 11:09:56

Willy Vanhaelen

With contiguous ranges you can use this one-liner:

Sub NoSpaces()
Selection = Evaluate("SUBSTITUTE(" & Selection.Address & ", "" "", """")")
End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.