Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I recently moved from Office 2007 to 2010. I have worked thru a number of issues with the differences between the two versions but one problem still has me stumped.

    I do a lot of cut and paste from a mainframe terminal emulator session into Excel. Specifically I cut a column of numbers off my terminal session and past them into a column in Excel. Excel see the numbers as TEXT but correctly stores them as numbers in the spreadsheet, and they are fully functional for calculations. The problem is that Excel treats leading blanks as a separate TEXT field, and instead of filling a single column in the spreadsheet with numbers, any number with a leading blank is pasted across two cells, with the left cell containing the blanks, and the right cell containing the number. Since my numbers will typically have varying numbers of digits, and the numbers, as displayed in my terminal session are aligned on the decimal point, all but the longest number will have some leading blanks spaces.

    Excel 2007 had a paste option that ignored the leading blanks and pasted the numbers properly into a single column. 2010 offers me only the options to paste "Unicode text" or "Text". Both these options split the leading blanks and the digits across two columns.

    NOTE: this problem is not unique to data copied from my terminal emulator screen. I have the same problem if I copy a similarly formatted column of numbers (i.e. containing leading blanks) from a NOTEPAD document. This leads me to believe the leading blank issue is in excel.

    Does anyone know a solution to this problem?
    thanx

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Mike,

    I don't have 2010 so I can't check this out but you can.

    After you paste your data click on the Paste Options Icon down-arrow.
    Select: Use Text Import Wizard...
    Select: Fixed Width
    Click: Next
    Click: To the RIGHT of the last digit in the 1st value
    Click: Next
    Click: Finish

    This works in 2003 & 2007 hope it works in 2010 for you?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That option is available in 2010

    But copying data with leading spaces from notepad, I do not get the issue with spaces
    splitting into columns it just puts them all in as numbers OK, in first paste column
    But I am ONLY trying to paste one column
    With multiple columns I have to use the Wizard Fixed as per RG to make sure the data is correctly parsed.

    Don't suppose you would be able to upload an example of the offending text list?
    Andrew

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek: thanks for the sugggestion... This does work. I recorded a macro following your suggested steps. When I looked at the source for the macroI found this:

    Sub PasteNumeric()
    '
    ' PasteNumeric Macro
    ' Use Import Text Wizard to paste text as numeric using the Fixed Width option
    '
    '
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
    DisplayAsIcon:=False
    End Sub

    this is interesting for two reason: Prior to creating this macro, if I simply did "Paste Special", "Unicode Text", I got the split column problem (i.e. lines with leading blanks put the blanks in the left column, and the digits in the column to the right). This was the same result I got with a simple "Paste Special", "Text",

    Also, the macro that I had recorded under an earlier version of excel (2003 or 2007) looked like this:

    Sub PasteCSV()
    '
    ' PasteCSV Macro
    ' Macro recorded 02/22/2007 by Mike Parker
    '

    '
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

    End Sub

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Whoops... Hit the wrong button before I was finished...

    The only difference between PasteNumeric & PasteCSV seems to be the Format parameter of the former specifies Unicode in addition to TEXT.

    I think there is something more going on here that is not obvious, because after recording PasteNumeric, and using it a couple of times, PasteCSV is now producing identical results.
    But I have seen similar eratic results previously. I start a new spreadsheet each month to collect my stats. the first few days this month the macros were consistent in producing the split column results. Yet, yesterday, the same PasteCSV macro was producing only a single column just as it had under Excel 2007. Today: split columns again.

    I suspect either a bug in the Excel code, or something in the source data. It could be something that I am doing. but since I was always using the macro for the paste (it saved me a couple of keystrokes), I am at a loss to know what that might be. If I got consistent results, right or wrong, I could live with it. But this on-again, off-again behavior tells me something somewhere is not right.

    Thanks for your help. I will try using the new macro from now on, and see if I get consistent results.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You haven't been using the Text to Columns feature have you? Those settings persist and can affect pasting.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jun 2010
    Location
    philippines
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Mike Parker View Post
    I recently moved from Office 2007 to 2010. I have worked thru a number of issues with the differences between the two versions but one problem still has me stumped.

    I do a lot of cut and paste from a mainframe terminal emulator session into Excel. Specifically I cut a column of numbers off my terminal session and past them into a column in Excel. Excel see the numbers as TEXT but correctly stores them as numbers in the spreadsheet, and they are fully functional for calculations. The problem is that Excel treats leading blanks as a separate TEXT field, and instead of filling a single column in the spreadsheet with numbers, any number with a leading blank is pasted across two cells, with the left cell containing the blanks, and the right cell containing the number. Since my numbers will typically have varying numbers of digits, and the numbers, as displayed in my terminal session are aligned on the decimal point, all but the longest number will have some leading blanks spaces.

    Excel 2007 had a paste option that ignored the leading blanks and pasted the numbers properly into a single column. 2010 offers me only the options to paste "Unicode text" or "Text". Both these options split the leading blanks and the digits across two columns.

    NOTE: this problem is not unique to data copied from my terminal emulator screen. I have the same problem if I copy a similarly formatted column of numbers (i.e. containing leading blanks) from a NOTEPAD document. This leads me to believe the leading blank issue is in excel.

    Does anyone know a solution to this problem?
    thanx
    when you right click on excel 2010 you get a lot of options paste special have you tried all of those? you can play around it

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    I seem to recall somewhere that Excel might use the first 25 or so record rows to determine certain options, so the odd behaviour you report may be something to do with the actual first x rows of data.

    If you said that you get different behaviour with EXACTLY the same input source data I would be concerned.

    zeddy

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for all the suggestions. I have tried a variety of paste options. The most disconcerting part of this problem is that sometimes my macros work properly (i.e. as they always did under excel 2007) and sometimes they "split" a line with leading blanks in the ;eft cell, and the number in the right.

    I am not certain what "rory" meant by "Text to Columns feature". the pasting I am having the problem with is copying a column of numbers from a text document and pasting them to a column in the excel spreadsheet. I am not "transposing" a row of numbers to a column numbers. However, at other times, I do copy a column of numbers from an excel worksheet and transpose them to a row in another worksheet. but the source is already in excel, not in a text document.

    I think "zeddy" may be on to something. I have begun to think that the results are somehow context related. I work with a lot of different spreadsheet, often having several open at a time. I am beginning to wonder is something is being "remembered" across spreadsheets, or based on "recent" paste activity?

    Thanks again for the comments and suggestions. I'll continue to return to this discussion periodically to see if anyone has new insight.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I mean the Data-Text to columns feature, used for parsing information into separate fields. I have a suspicion (but can't recall for sure) that the settings may also persist if you have used the Data-Import from Text file feature.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Rory, I think you are on to something in your 8/20 response. sorry, I hadn't checked this thread for a while, having given up in frustration.

    I do import some text files (tabular reports) before doing the cut and paste that has been causing me problems. The leading blanks were causing problems again: numbers with leading blanks pasted with the blanks in one column, and the digits in the next column to the right.

    But if I closed Excel completely, and then reopened it, the paste feature worked without problem: The leading blanks were essentially ignored, and all numeric values, regardless of leading blanks, pasted into the same column.

    Your explanation would seem to explain the seemingly erratic nature of the problem. I'll watch for this closely for a while tos ee if closing the program before working on this one spreadsheet cinsistently solves the problem.

    thanks

    mp

  12. #12
    2 Star Lounger
    Join Date
    Jun 2010
    Location
    philippines
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Mike Parker View Post
    Whoops... Hit the wrong button before I was finished...

    The only difference between PasteNumeric & PasteCSV seems to be the Format parameter of the former specifies Unicode in addition to TEXT.

    I think there is something more going on here that is not obvious, because after recording PasteNumeric, and using it a couple of times, PasteCSV is now producing identical results.
    But I have seen similar eratic results previously. I start a new spreadsheet each month to collect my stats. the first few days this month the macros were consistent in producing the split column results. Yet, yesterday, the same PasteCSV macro was producing only a single column just as it had under Excel 2007. Today: split columns again.

    I suspect either a bug in the Excel code, or something in the source data. It could be something that I am doing. but since I was always using the macro for the paste (it saved me a couple of keystrokes), I am at a loss to know what that might be. If I got consistent results, right or wrong, I could live with it. But this on-again, off-again behavior tells me something somewhere is not right.

    Thanks for your help. I will try using the new macro from now on, and see if I get consistent results.
    something to do with what you are pasting

  13. #13
    New Lounger
    Join Date
    Sep 2010
    Location
    MI, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bong tubera View Post
    something to do with what you are pasting
    Although it has a little to do with what you are pasting, I have found that it can respond differently to the same data depending on what you have done previously with Excel. Just as Mike has said, you can take the same data that didn't work properly and reopen Excel and it will respond differently. The problem has more to do with what operations (cut/paste/imports etc.) you have done previously just as rory has suggested. I have never spent a lot of time trying to determine the exact cause of the different behaviors. I solved the problem myself by simply writing macro code to check how a paste operation was stored and then wrote two different routines to handle the two differently formatted paste cases. My macro code would read the data differently depending on how it was pasted which was determined by the macro.

    Since you (Mike) may not be a programmer (coder), you may not have this option of doing this. But at least you have found a workaround by closing Excel and reopening it to make sure the data is consistently pasted into it when you use it. To be honest, I don't like it when Microsoft implements inconsistent responses due to keeping the settings last used. IMO, MS should always use the same technique regardless and force people to specify a different behavior than the default if they so desire. Otherwise you end up with mysterious and seemingly unexplainable behaviors such as this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •