Solved

Stop excel from splitting imported text containing commas

Posted on 2010-08-24
5
487 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
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 200 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 300 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

816 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

10 Experts available now in Live!

Get 1:1 Help Now