Carriage Returns in Excel
February 10, 2006 2:02 PM Subscribe
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:
=SUBSTITUTE(K2,CHAR(010),"")
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.
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:
=SUBSTITUTE(K2,CHAR(010),"")
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.
Response by poster: I had a look at Word. It suggests ^p to find a paragraph break. That's not giving me any matches on my data.
I also tried ^l, ^m and ^b for line breaks or whatever.
posted by godawful at 2:21 PM on February 10, 2006
I also tried ^l, ^m and ^b for line breaks or whatever.
posted by godawful at 2:21 PM on February 10, 2006
Best answer: create a second column =SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10),"<br>"). This will handle both UNIX and Windows carriage return-line feed combinations.
posted by patrickje at 2:22 PM on February 10, 2006
posted by patrickje at 2:22 PM on February 10, 2006
Godawful,
I think I could write the VBA to fix this problem, but I'm not sure I understand what's happening.
The article is in column "K", right? You need to replace each carriage return with the HTML paragraph code? (IE: replace CR with "p" in brackets)?
If so, I think I might be able to tackle this for you (assuming the Windows VB code will port properly to the Mac).
posted by MotorNeuron at 2:26 PM on February 10, 2006
I think I could write the VBA to fix this problem, but I'm not sure I understand what's happening.
The article is in column "K", right? You need to replace each carriage return with the HTML paragraph code? (IE: replace CR with "p" in brackets)?
If so, I think I might be able to tackle this for you (assuming the Windows VB code will port properly to the Mac).
posted by MotorNeuron at 2:26 PM on February 10, 2006
Response by poster: Cheers Patrick, that formula has found the paragraph breaks. However, it's not replacing them with <br>. Any idea why not?
posted by godawful at 2:28 PM on February 10, 2006
posted by godawful at 2:28 PM on February 10, 2006
Response by poster: When I say it's not replaced them, I mean, they're gone, but the new string of text is without the <br>s.
posted by godawful at 2:29 PM on February 10, 2006
posted by godawful at 2:29 PM on February 10, 2006
Best answer: try this =SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),""),CHAR(13),"<br>") I had my carriage return and line-feed mixed up.
posted by patrickje at 2:35 PM on February 10, 2006 [1 favorite]
posted by patrickje at 2:35 PM on February 10, 2006 [1 favorite]
Response by poster: Thanks very much MotorNeuron. Your description of the problem is accurate. If you still want to try, that would be great. (I'm a complete VB Newbie, however).
posted by godawful at 2:35 PM on February 10, 2006
posted by godawful at 2:35 PM on February 10, 2006
Response by poster: Brilliant, Patrick. I owe you one.
posted by godawful at 2:38 PM on February 10, 2006
posted by godawful at 2:38 PM on February 10, 2006
« Older A raw-IP/HTTP source for real-time option quotes? | Implementing Virtual Reference Desk on a closed... Newer »
This thread is closed to new comments.
posted by grateful at 2:16 PM on February 10, 2006