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.