• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

Stop excel from splitting imported text containing commas

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
Terrygordon
Asked:
Terrygordon
2 Solutions
 
andreyman3d2kCommented:
Could you upload the offending workbook?
0
 
bouscalCommented:
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
 
TerrygordonAuthor Commented:
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
 
stevericeCommented:
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
 
TerrygordonAuthor Commented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now