Solved

Excel with carriage returns

Posted on 2015-01-07
7
130 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:
SimonAdept earned 500 total points
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Richard Korts
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 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