Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stop excel from splitting imported text containing commas

Posted on 2010-08-24
5
Medium Priority
?
495 Views
Last Modified: 2012-05-10
Hi all

I am pasting text from the clipboard into an excel sheet. The text is crossword clues which often have commas in the sentence and at the end if there are two words in the answer e.g. (3,4). The problem is that when I paste the text excel treats the commas as delimiters and splits the sentences and letter counts into separate cells wherever there is a comma in the sentence. I want it to put the whole sentence in one cell, including the commas.

To make matters worse, it is doing this in one of the three worksheets, but not in the others. I have tried formatting the cells in the rogue sheet as text and general, but to no avail. I am using excel 2007, but the workbook is 2003.

Any help would be greatly appreciated.

Regards

Terry
0
Comment
Question by:Terrygordon
[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
5 Comments
 
LVL 6

Expert Comment

by:andreyman3d2k
ID: 33515001
Could you upload the offending workbook?
0
 
LVL 7

Assisted Solution

by:bouscal
bouscal earned 800 total points
ID: 33515158
pasting text is not the same as importing.  
Have you tried pasting your info to notepad, saving it, then attempting to import the data using the import wizard?
0
 

Author Comment

by:Terrygordon
ID: 33515515
Bouscal - I have tried pasting to a text file and then copying and pasting to the sheet and that seems to work fine. I suppose this indicates that the issue is with the crossword compiling software and the way it is formatting the text when it exports to the clipboard.

Andreyman - The spreadsheet is full of macros linked to my crossword compiler software, so it probably wouldn't be much use uploaded on its own, as you wouldn't be able to export from the software to the clipboard.

I'm sure it says in the Excel help files that the delimiter character is set to comma by default, but it doesn't say how or where this default can be changed (except of course in the text to columns wizard).

Bouscal's solution is a good workaround, but I would prefer to solve the problem within Excel. If no other suggestions appear by tomorrow, I will award the points to Bouscal.

Regards

Terry
0
 
LVL 6

Accepted Solution

by:
steverice earned 1200 total points
ID: 33516179
Have you tried 'Paste Special'>'Text'?

Alternatively, another workround would be to paste your text and allow excel to split it at the commas, and then use a concatenate formula to re-join the text into a single cell.

 =trim(concatenate(a1," ",b1," ",c1))

The " " ensures that spaces are entered between each data element and the 'TRIM' removes any extra spaces
0
 

Author Comment

by:Terrygordon
ID: 33519102
Hi All

For some reason the issue has resolved itself. I was pasting the same data this morning, intending to use concatenation, and inexplicably it didn't split at the commas. How weird is that?

Anyway, both workarounds are useful and viable ways to solve the problem if it happens again.  Steve's solution is more efficient as it avoids having to use the intermediate document, so I am splitting the points accordingly.

Thanks for your help.

Terry
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 developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

719 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