carriage returns in text

  • Thread starter Thread starter Janet
  • Start date Start date
J

Janet

I am exporting data from another database through (TOAD)
and putting into an XLS format. Everything comes over fine
except for a long field and it contains vertical
rectangles in the body of the text. It looks to be
everywhere where there was a hard return. How do I get rid
of these symbols? Replace doesn't work because it does not
recognize it as a character. Turning outline symbols off
doesn't work either. Any ideas?
 
Gord Dibben posted this solution Janet

Select your range of data.
Edit>Replace

What: ALT + 0010(from the number pad keys)
With: enter a space or leave this blank

Replace All.
You may have to enter ALT + 0013 instead of 0010.
Note: when you type in the 0010 you won't see anything, but it is there.
 
Janet said:
I am exporting data from another database through (TOAD)
and putting into an XLS format. Everything comes over fine
except for a long field and it contains vertical
rectangles in the body of the text. It looks to be
everywhere where there was a hard return. How do I get rid
of these symbols? Replace doesn't work because it does not
recognize it as a character. Turning outline symbols off
doesn't work either. Any ideas?
Click to expand...

Is this something you are doing regularly or just one-off. If one-off, how
about deleting them? Otherwise, have you tried the Clean function, yet?

Geoff
 
Great! - had thesame problem for ages, and now I know how
to find the cells with those characters.

Problem is that the replace does not work!

I get a message "Text is too long" and it does not replace
or allow me to continue.
I can do it manually, but this is a repetitive task.
Any Ideas?
 
Dave Peterson posted this.
Try it (post back if it is working for you or not)


I've never had good luck with the alt-0013 (carriage returns).

I'd use a macro:

Option Explicit
Sub testme()
With ActiveSheet
.Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub
 
I don't think that'll help for the "formula too long" error.


If you're using xl2k or xl2002, this might work:

Option Explicit
Sub testme01()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = Replace(myCell.Value, Chr(10), " ")
Next
End Sub

If you're using xl97, change that Replace( to application.substitute(

And like Ron wrote, if you have carriage returns (alt-0013), change that chr(10)
to chr(13).
 
Back
Top