Solved

Excel with carriage returns

Posted on 2015-01-07
7
141 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

735 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