Re: Find a carriage return in a worksheet

Giganews Newsgroups
Subject: Re: Find a carriage return in a worksheet
Posted by:  David McRitchie (dmcritch…@msn.com)
Date: Wed, 12 May 2004

And what do you want to change them to  CRLF  or a space.

The following will remove all  CRLF (x'0D10')  CR  (x'0D')  and LF (x'10')

Sub Remove_CR_LF()
  Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
  Selection.Replace What:=Chr(13)&Chr(10), Replacement:=Chr(32), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
  Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
  Selection.Replace What:=Chr(13), Replacement:=Chr(32), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

You can TRIM each cell in which case look at
  http://www.mvps.org/dmcritchie/excel/join.htm#trimall
You would proabably want to use the  application.TRIM

The  use of CLEAN would  probably destroy your data, as it will
remove the characters and there will be no separation between words.

If this is going over to a mainframe for batch processing,  seems
they should be able to fix up this kind of thing when they run their job. .

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:      http://www.mvps.org/dmcritchie/excel/search.htm

"VW" <anonymo…@discussions.microsoft.com> wrote ...
> I have a big Excel file that has been saved as a CSV file.
> We need to upload this file to SAP. However, some cells
> contain a carriage return (it is displayed as a square in
> the cell). I need to fix all the rows that have the
> carriage return. Is there a way to FIND a cell with
> carriage return ? ie. by using the FIND function.

Replies

None

In response to

Find a carriage return in a worksheet posted by VW on Tue, 11 May 2004