remove CR, CR.LF?

[guthrie]

I have a bunch of data copied from an excel export in ACT, and for some reason this time many fields show up as two lines, the data and then a blank line (in the edit bar).
I can put the cursor on the second blank line and backspace, and then it shows up properly, and all is well.
I can also tell something is there, as formatting shows it as two lines, and also len() shows one too many characters present.

I have seen many articles on how to replace LF using Ctrl-J, but that doesn't match anything. And trying to enter Ctrl-J in the Find box, shoes not show anything. (Should show a dot?).

However doing a "=SUBSTITUTE(F5,CHAR(10),"")" does seem to work, but would be a lot of trouble to do for all columns of data to change and then copy over original data.
Some places indicate that a CR/LF is different, but I don't know how to deal with that.

Is there some way to actually see what the hidden formatting characters are in the cell(s)?, and then to Find/replace to truncate?

 

[guthrie]

I also note that copying some cells into Word, I see some odd square character at the start f the line(?)

And using "clean()" will also fix things, but would require a lot of copy and overlay of everything, and in any case I'd like to know what is going on.

.

 

[kvsrinivasamurthy]

Have you tried Find and Replace.
Find--> CHAR(10)
Replace with--> (don't type anything)
Replace all

 

[arthurbr]

Have a go at Cleaning "dirty" data

 

[guthrie]

Have you tried Find and Replace.
Find--> CHAR(10)
Replace with--> (don't type anything)
Replace all

Hmm, entering "Char(10)" won't work, as it then searches for thatstring, not what it may mean.

 

[guthrie]

Have a go at Cleaning "dirty" data

Thanks - I already noted thai I can use a simple "clean()" to fix it, but that means creating a temporary cleaned coy of every column, and then overlaying the original one.
I was looking for a way to select all cells, and use find/replace.

 

[kvsrinivasamurthy]

Try this macro

Sub RemoveCR()
ActiveSheet.UsedRange = Evaluate("Substitute(" & ActiveSheet.UsedRange.Address & ",char(10),"""")")
ActiveSheet.UsedRange.WrapText = False
End Sub