Excel - How to copy and paste data from txt file into one column?

[arvarr]

Dear All
How do i copy and past data from txt file into one column in Excel.

Day 1 - Copy and Paste Data from txt file into Excel, works well, everything falls into one column.

Day 2 - Copy and Paste Data from txt file into Excel, but it seems that data is populated into multiple columns.

No setting have changed, the data is exactly the same.

What went wrong? Can anyone kindly advise?

Thanks.

 

[SkipVought]

hi,

Please post a sample of the data that falls into two columns.

I'm guessing that there is a default delimeter character like a COMMA, that is causing a parsing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

I'd use file-open (*.txt), choose delimited, but untick all boxes with delimiters.

 

[arvarr]

Hi
The data is usually created in the system and there are two ways for me to copy and paste it.

1. As per above, file-open without delimiters and this works.
However, it is not viable to do so as i have a lot of files which I generated via the system and i usually just open the file output on screen and then paste them directly into Excel.


2. The other method is to open the output file on screen and then copy and paste them directly into Excel.

Say for example:-
The below data is just a test that i copied from notepad and when i copy and paste them into excel, it is putting them into two columns instead of one.

test file 1234567890
test
test

test file

testing 123

123 testing


Thanks for any help.

 

[SkipVought]

If it were me, I would use Data > Import External Data > Import -- Fixed Width with no parsing defined.

This way, you avoid having to get you data into Excel using Copy 'n' Paste. If your text file always has the same name, all you need do is Data > REFRESH. If the text file has different names, make sure that the Prompt for file name is checked.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
It would be great if there was only 1 or 2 files.

The thing is that there are many reports that I run and it would be a pain to save all the files and then open it again.

Do you know if there is any settings problem or something that I am not doing correctly that its not allowing me to copy and paste into one column.

The strange thing is that it copy and paste into one column at one time and not the next time. So, not sure what is wrong. The above example that I provided you does not have commas so how come it is not putting it into one column.
The above is only an example so dont be limited to it. Any other advice will be greatly appreciated.

Thanks.

 

[SkipVought]

I'd put all the text files alone in one folder.

Then use VBA to loop thru the files in the folder, IMPORT, append to existing data, next.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

Day 2 - Copy and Paste Data from txt file into Excel, but it seems that data is populated into multiple columns.

This is likely caused because of numerous spaces between words. Excel "thinks" you want to start another column.
Try using a find and replace -- any time 2 spaces appear together, replace with one space. You might have to run this several times.


Randy

 

[arvarr]

Hi Skip
Thats a good suggestion but without wanting to go into vba at this stage because the thing is that it sometimes works and sometimes not so it is a very strange thing.

Hi randy700
Tried using an example with more than one space but it is actually populating into one column instead of multiple columns.

Example: Copied from txt file in notepad and paste into excel and it all goes into one column. These are not actual data but just trying to see if multiple spaces are creating the issue.

1111 111
111 111
1 1
1
1 11

Thanks guys for all the efforts. Not sure if we're gonna get to the bottom of it.

 

[SkipVought]

...that it sometimes works and sometimes not...

What VBA or your process?

I know how to make it work every time. Did you not try it? You say there are too many for IMPORT, but it's NOT too many for copy 'n' paste? HUH? What! You must like the pain.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Yes, I understand that I can loop through the files but at the same time, I just wanted to know what causes this issue when i copy and paste, it does not seem to be the same result all the time.

There is a solution, which i greatly appreciate.

But, what is the root cause of the problem.

Thank you.

 

[SkipVought]

It depends on the state of Text to columns parameters. THESE CAN CHANGE!

Therefore, do not rely one copy 'n' paste UNLESS you adjust these parameters before you CNP.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Ok, no problems at all.
Thanks for your input and everyones input.
Greatly appreciated.