(No Ratings Yet)
Loading ...How to remove or convert CR/LF embedded in Excell cells e.g. when Outlook data is exported?
I have an Excell spreadsheet that was exported from outlook. some cells appear to have carriage return/line feeds embbedded. This screws up when I need to create a CSV file.
Posted in 1896 | 
Tags: carriage, carriage return, cells, cr lf, CSV, csv file, Excell, excell spreadsheet, file, Line, Outlook, outlook data, return, screws, spreadsheet
One Response to “How to remove or convert CR/LF embedded in Excell cells e.g. when Outlook data is exported?”
There may be a better way, but when I had a similar problem I never found one, so I eventually resorted to this VBA macro sledgehammer to fix it.
Are you at all familiar with the VBA underbelly of Excel? If not, then I’m just going to ask you to trust me.
Er, hopefully these keyboard and menu commands haven’t changed much through the different versions. I’m using Excel 2000 as my reference, though.
1. From your Excel sheet, press Alt-F11 to enter the VBA environment (that’s the Alt key and the F11 key simultaneously).
2. Look for the subwindow with “Project” in its title bar. Press Ctrl-R if you don’t see it already open.
3. Look for “VBAProject” in that window. Right-click on it, and select “Insert” –> “Module”.
4. Hopefully you will now have an empty code window open. Into it, copy and paste the following, from the “sub” to “end sub” lines, inclusive.
Sub RemoveCrLfs()
Dim pobjCell As Range
Dim plCharCounter As Long
Dim psCellText As String
For Each pobjCell In Selection
psCellText = pobjCell.Text
Do While InStr(psCellText, vbCrLf) > 0
psCellText = Replace$(psCellText, vbCrLf, vbLf)
Loop
pobjCell.Value = psCellText
Next
End Sub
5. Exit the VBA environment (via the “File” menu, “Close and Return to Excel”)
Congrats! The hard part is done. Now you should see a macro under yout “Tools” –> “Macro” –> “Macros” menu called RemoveCrLfs.
Select the cells you want to process, and then run the macro. What it’s doing is looking at the text in every individual cell in your range of selected cells, and if there is a carriage return/linefeed combination, it replaces it with merely a linefeed, which is how Excel *normally* represents multiline text in a cell.
If you don’t want ANY sort of line break, you can just change this line in the sub:
psCellText = Replace$(psCellText, vbCrLf, vbLf)
… and change vbLf to something more appropriate. If you just want it to be a space, for example:
psCellText = Replace$(psCellText, vbCrLf, ” “)
Personal experience
Report this comment