Find a carriage return in a worksheet

  • Thread starter Thread starter VW
  • Start date Start date
V

VW

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

Select one of the cells with the carriage return. Copy it from the
"Formula Bar" then Enter. Now select your data and go to Edit>Replace
paste in the carriage return to the "Find What" box and leave the
"Replace With" Blank

or, try using the CLEAN function. Then Copy, paste special - values.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
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. .
 
Back
Top