Removing Spaces

Just a note in general about importing externally sourced data that can have variably formatted contents: I suggest that you use Power Query. Its intended use is exactly this - ETL or Extract, Transform, and Load. I've had to contend with files that contain the Alt-Enter just as you describe plus the other types of things that users will put into a file including what I consider the bane of Excel existence, "Merge & Center".

One of the big things I love about Power Query is that effectively its steps are an audit of the steps that are followed.

Plus for your use you can build a solution that when you refresh the data it can process the input files based on their location.

A lot of comments and macros. Ctrl+H = "Find/replace all" which is a lot simpler - Find " ", replace ""

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

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.

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.

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

***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. ) )

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

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

@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 .. !!!! :) )

Well you will just have to believe me

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}]

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}]

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.

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

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

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}]

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

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)

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.

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

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

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), "")

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

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.

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

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

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

With contiguous ranges you can use this one-liner:

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