Excel with carriage returns

See http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28592296.html.

This question did NOT provide an answer that worked; I erroneously awarded points. It seemed to work but did not; the saved tab delimited sheet still has the issue.

I'm thinking that the character should be 013, not 010.

I also tried the Macro suggested as the last comment; does not work.

An Access person wanted me to zip & post the Access database from whence the Excel came; I cannot do that, the database contains sensitive company information.

Any ideas, either in an Access query or in Excel on how to remove carriage returns from fields will be appreciated.

Can't I just remove the carriage return stuff in an Access Query?

Thanks
Richard KortsAsked:
Who is Participating?
 
SimonCommented:
Rey's suggestion from your previous question should work if you modify your Access query definition:

select f1, replace(replace(replace([f1], chr(13) & chr(10)," "),chr(13), " "),chr(10), " ")

From what I can see it is just one column (x) that has the problem. Your files don't have column headers so I can't see the field name, but it is that field name / column that you should modify in your Access query.

.e.g. say that field is called 'ServiceNotes'

you'd change that column in the query to
replace(replace(replace([ServiceNotes], chr(13) & chr(10)," "),chr(13), " "),chr(10), " ")

If you wanted to make the change in Excel, NBVC's suggestion should also work, but maybe make it a bit broader

Sub RemoveChar()
With ActiveSheet.UsedRange
    .Replace Chr(10), " "
   .Replace chr(13), " "          'Added carriage return
End With
End Sub

When I tested the above code on your Excel file it was chr(13) that did the trick.
0
 
Richard KortsAuthor Commented:
To SimonAdept

Thanks, I'd like to try the macro in Excel, but it won't let me edit the existing one which only does 010.

What is the editing trick?

On the Access way, see the attached. The Query Builder DOES NOT LET you change the items in the Field row. Can you show me a SPECIFIC example of how to do this?
build-query.jpg
0
 
SimonCommented:

1. You should be able to edit the Excel macro (or create a new one with a different name)

Use the Developer tab to access the VBE, or choose Tools/Macros, select the relevant macro then choose step into or edit.
If preferred, simply copy and paste the code into a module in VBE and change the name of the sub (so that it doesn't complain of a duplicate
Sub RemoveChars_10_13()
With ActiveSheet.UsedRange
    .Replace Chr(10), " "
   .Replace chr(13), " "          'Added carriage return
End With
End Sub

Open in new window


2. To modify columns in Access query builder...

In Access Copy this to the clipboard first:
ServiceComments_1:replace(replace(replace([ServiceComments1], chr(13) & chr(10)," "),chr(13), " "),chr(10), " ")

Open in new window

Then select that field in the query builder and paste from the clipboard over the top of it. You CAN change the items in the field row, but you just need to be aware of a couple of rules:
If you're not directly using a field from the table, you need to prefix it with an alias (default is "expr1:")
Your alias cannot be the same as the field title (i.e you have to call your expression "ServiceComments_1" instead of of "ServiceComments1"
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Richard KortsAuthor Commented:
To SimonAdept,

I figured out the Excel Macro thing.

I added the 13 & ran it, then I saved it & then saved it as tab delimited.

PERFECT, no extra strange rows.

Thanks!
0
 
SimonCommented:
Cool. Glad you got it sorted. I'd suggest that the best resolution would be to fix the Access query so you don't have to remember to run the macro each time (and it would be good learning).

I think you'd only have to fix the two ServiceComments fields. It is usually memo fields from Access that have the linebreaks in them.

Your choice anyway :)
0
 
Richard KortsAuthor Commented:
To SimonAdept,

Yes, that would be better, but presumably this is the "last time" we are about to go live with a web based system, MySQL, custom php programming, etc.

We are doing parallel testing this month; if it looks good, we go live with the web system in Feb.
0
 
SimonCommented:
LOL - I agree there's no point in spending time on it if it's about to become obselete... I just laughed because you put "last time" in quotes, and I've been in a few scenarios where the "last time we'll have to do this" kept recurring like Groundhog Day. Good luck with your new system, send your MySQL and php queries this way!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.