I’m writing this rather technical mumbo-jumbo here because I need it from time-to-time, and I always forget it.
There comes a time when I need to do some data manipulation within Excel and import the results into a database. Mostly, I don’t have data in Excel that is overly complex, and so doesn’t pose any particular challenge in doing what I want. Today I’m working on an Excel document in Office 2007 – not one I made, but rather someone else’s wretched representation of data. It has carriage returns within the fields themselves, and I need to clean those out before importing the data into the database. There are a variety of ways to do it outside of Excel, but not a straightforward method within Excel. Microsoft’s search & replace feature in Office 2007 does not appear to be any better than in the former products. Is a little regular expression handling too much to ask for?!? So here are the goods:
If you turn off “word-wrap” on the offending cell, you should see a small box in the field where a carriage return once hid itself. Excel 2007 doesn’t simply allow me to copy this character and use it to search the worksheet. Neither does it acknowledge any sort of ^p, \r\n, or vbCRLF. Instead, you need to key in the ANSI code for the character you wish to search (and replace) on. In this case, I searched for ALT+0010 (the linefeed) and replaced it with a space. You may need to search for ALT+0013 ALT+0010 (CRLF) if this doesn’t work for you, but in my case removing the LF seemed to have worked.
Microsoft Excel is a dangerous tool. I try to replace Excel spreadsheets with Access 2003 applications wherever I can because I can lock away all of the code and formulas in a .mde file and not worry about people changing the formulas, reformatting the cells or typing in crazy stuff. I don’t mess with the accountants though. They need spreadsheets like a junkie needs junk.
I’m attempting to search a large file and replace the CRLF with a ; but Excel will not let me insert
the CRLF into the search and replace box? Any idea? I’m using Excel 2003 though – are there any
other good tools to do this?
I seem to remember Excel 2003 being slightly more forgiving in this arena and allowing some other trickery to search & replace CRLFs. However, I just tried my above instructions from Excel 2007 and they worked perfectly. I should note, typing in ALT+0010 won’t show you anything in the “Find” box. It takes a little faith at this point. Also, typing in ALT-key codes generally requires the use of the left ALT key on your keyboard. That, and you have to use your number pad instead of the numbers above your keys. This is not Excel specific, it’s just the way you type in ALT-key codes. Let me know if this solves your problem. If not, I have some other ideas on how our setup might differ.
I’m using 2003 and using vba code. The error that I am getting is when I try to populate a cell with over 4000 characters and crlf’s sprinkled in. it doesn’t allow it. Any help appreciated.
From MrExcel:
Use a VBA Macro
Sub bbb()
Cells.Replace what:=vbCr, replacement:=";"
Cells.Replace what:=vbLf, replacement:=";"
Cells.Replace what:=vbCrLf, replacement:=";"
End Sub
enjoy
Just a note. Although the alt+0010 won’t show up in the find text box, It will show up as a box in the drop down list.
The CLEAN(cell) function will strip non-printing characters from the cell referred to. CRLF and tab chars within the cell cause me the most problems with exporting from Excel via tab-delimited text to other programs.
Just create a new worksheet, put =CLEAN(‘sheetwithdata’!A1) in cell A1, copy to clipboard, then go to the cell matching the last cell in the data sheet e.g . Ctrl-G AT1285, mark the whole range then paste the formula into the whole range.
Then copy the now-clean data from this worksheet, go back to the original worksheet and do a Paste special, Values.
Et wala! all the CRLF, tabs and other pesky characters are gone!
Hi,
Thanks for posting this tip. I was trying to export my query results from SSMS into Excel, but SSMS was stripping my CRLF via copy/paste. I replaced my CRLF with a string literal “^CRLF^” with carat delimiters so that I can copy/paste into Excel. After that it was just a matter of search/replace “^CRLF^” with the ASCII codes. This is a bit of a hack, but I don’t think there’s a way around it.
Open Sear & replace. In the Find What inpuit box, type Ctrl-J (you will see a tiny . shown). Leave Replace with as empty. Do a replace all. This replaces all CRLF with a nothing.