Solved

Excel with carriage returns

Posted on 2015-01-07
7
131 Views
Last Modified: 2015-01-07
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
0
Comment
Question by:Richard Korts
  • 4
  • 3
7 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40536830
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
 

Author Comment

by:Richard Korts
ID: 40536881
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
 
LVL 18

Expert Comment

by:Simon
ID: 40536906

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Richard Korts
ID: 40536911
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
 
LVL 18

Expert Comment

by:Simon
ID: 40536918
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
 

Author Comment

by:Richard Korts
ID: 40536926
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
 
LVL 18

Expert Comment

by:Simon
ID: 40536941
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now