It's not how I'd choose to spend my Friday evening, but for the last few hours, I've been trying to replace carriage returns that have turned up in an Access and Excel file, and drawing a blank.
I've done a thorough search of the archive, and I don't think this has come up before. I've also googled around and looked at specialist forums to no avail.
So, someone's kindly entered the text of about 200 articles into an Access database. They're destined for a website. Each one is paragraphed with what I assume are standard carriage returns which I need to turn into html paragraphs. (Once this is done, I plan to somehow import them articles and their metadata into the right columns of a mysql database.)
At the moment, I'm working on this data in Excel, which was the only way I could find to access it on my Mac. I'm also more au fait with Excel than Access.
I've tried a find and replace on the column of article texts, only to find that there's no way to tell Excel to find a return.
Another horrible hack I thought up was to use 'Text to Columns' using the returns as a delimiter. I could then have inserted tags and concatenated the new columns back together. Again I found no way to specify the return as a delimiter.
I found this
note on a way to specify character codes, but I couldnt' make it work in these two cases. Are Mac character codes different?
I tried to use these character codes by adding a column next to the article text column (which is column K) with this formula:
Again, no dice. It makes no substitutions at all.
Finally, I saved as a .csv, opened it in Textwrangler, and used an appropriate regular expression. It made the changes, but then I couldn't open the file in Excel. Something about a Sylk error.
If there's a simple way to do this in Access that I missed, I could go back to that.
Am I going to have to use VBA? There's a suggestion at the foot of this thread
, but I don't know how to make it work.
As always, I'll be very grateful for any help at all.