Excel 2016 - breaks existing functionality

[walvord]

I apologize for the vague title. I'm not sure what that functionality was called.

Previously to v2016, I could copy tabular data (fixed width, no delimiter) from a terminal and paste it into A1 of a given sheet. The data would paste in as 1 row per row and all in 1 column. I could then highlight column A and use text-to-columns to easily split it out by selecting fixed width and the typical next/next/finish wizard. However, it seems that this functionality either is disabled by default in v2016 or it was just removed.

I'm hoping someone could point me toward either what search term(s) I should be googling to find help or what settings I can change to return that pre-2016 functionality.

If I didn't explain it well enough, please feel free to ask additional questions.


thanks,
will

 

[RoryA]

Welcome to the forum.

What is happening currently when you paste? Also, do you have the 365 subscription version of Excel 2016?

 

[walvord]

I'm not sure about the 365 subscription. It's what my company put on my laptop. When I paste now, excel makes a best guess as to how to split the column headers and data into various columns. The problem seems to be that not all columns have data and excel gets tripped up and combines several headers into 1 column and also that some data in a given column might have commas and excel automatically splits that data as comma separated (which defeats the purpose of fixed width data imports).

As a work-around I just found, I can use the paste drop down under home on the ribbon and use the text import wizard. It's just really really disappointing since pre-2016 I could literally paste data and then go straight to column-to-text wizard to split it out. I'm not a big MS guy so I figure out an easy way to do something and rely heavily on it.

 

[RoryA]

I can’t recall how 2016 works but in most versions, if you’ve used text to columns in an Excel session, it will try to parse any pasted data using the same settings until you either restart Excel or use TTC again.

 

[walvord]

First, I really appreciate your willingness to help. Second, I haven't done a good job explaining myself.

What I used to do (pre-2016):
1) copy tabular data (fixed width, no column delimeter)
2) paste into A1 in a given sheet
-- here is where excel has the pasted data as 1 row per row of data and all data in a single column
3) select column A
4) do the column-to-text

what's happening differently in 2016:
After step 2, excel seems to be automatically parsing the pasted clipboard data treating the data as both fixed-width and comma separated. So if column c and d has no data, column header b has the column headers for b, c, and d are all in B1. Also, if E5 has data with a comma, that data gets parsed as comma separated data and something like "I, miss, Excel, 2012" would be split between E5, E6, E7, and E8. Basically, instead of all of the pasted data going into a single column, Excel is making unwanted assumptions and trying to intelligently parsing the data into multiple columns.


What I want to happen is to restore my ability of pasting data into A1 and have ALL of the data go into a single column without the failed attempt at intelligently parsing it.

 

[sandy666]

maybe it will help, try
- copy data from terminal
- select eg. A1
- right click in formula bar
- paste data
- use Text2Columns

 

[walvord]

That doesn't work, but I appreciate that you tried.

 

[sandy666]

You can try PowerQuery via txt file

 

[walvord]

Thanks Sandy. I'll keep that in my back pocket as an option. In the meantime, I have a work-around. It's just a more involved process.

Do you happen to know what the feature is called for the automatic parsing of the pasted data so I can search for whether I can disable it to return to the pre-2016 functionality?

 

[sandy666]

try this: How to stop splitting text to columns