Join 3,417 readers in helping fund MetaFilter (Hide)


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.
posted by godawful to Computers & Internet (9 answers total)
 
I know Word has a built-in tool for replacing special characters, but I don't know if you want to go down the path of porting this data through too many applications...
posted by grateful at 2:16 PM on February 10, 2006


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


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


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


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


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


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]


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


Brilliant, Patrick. I owe you one.
posted by godawful at 2:38 PM on February 10, 2006


« Older I'm looking for a free or paid...   |  I want to start a virtual refe... Newer »
This thread is closed to new comments.