?
Solved

Excel with carriage returns

Posted on 2015-01-07
7
Medium Priority
?
151 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 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