Solved

Stop excel from splitting imported text containing commas

Posted on 2010-08-24
5
485 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

920 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