Solved

Stop excel from splitting imported text containing commas

Posted on 2010-08-24
5
484 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
Comment Utility
Could you upload the offending workbook?
0
 
LVL 7

Assisted Solution

by:bouscal
bouscal earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

11 Experts available now in Live!

Get 1:1 Help Now