Copy/ Paste Cells With Multiple Lines Doesn't Work

NikkiAke
NikkiAke ✭✭
edited 12/09/19 in Smartsheet Basics

I've been digging through community for resolution to this issue, but I'm coming up empty.

I need to be able to copy/ paste multiple rows of data that have "multi-row (alt+enter)" information from Excel (and Smartsheet). Currently, when I try to copy and paste these "multi-row" cells, they spill into separate cells instead of staying together. It DOES work if I do them one at a time, but I have hundreds of rows of data - it's not a feasible solution to do it one at a time.

Is there a trick to being able to copy multiple rows and not have all of the information spill onto another separate row instead of the same cell? I've been using VLOOKUPs between Smartsheets to perform this task, but it is pretty painful.

Anyone have any tips??

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi NikkiAke,

    I'd recommend trying to import the excel sheet and then moving/copying the rows to the sheet instead.

    Would that work?

    Hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks for the suggestion. I have tried this and it is very hit or miss. :(

     

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    What's not working?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • It spills over the text into multiple cells instead of keeping it in one.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Ok.

    This is probably why.

    TIP: If you've copied a multi-line cell in Excel or other program, double-click on the cell in Smartsheet before pasting it in to prevent the lines from spilling over into the cells below.

    More info: https://help.smartsheet.com/articles/518318-tips-for-using-copy-and-paste

    Hope that helps!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I have this same issue, although this solution works for copying single cells, it is not sustainable for copying multiple excel columns.


  • I have the same issues than above and for some reason it was not happening before and suddenly it started. In my case I first export from a Smart-sheet into excel format in order to format the data to my liking, then i paste it into a smart sheet template or data source linked to multiple other sheets/graphs/metrics and reports which are actually feeding several different Smart-sheet Dashboards. This data source i update it manually by copy pasting the information in . Problem is that I have one column with Comments (it is the last one to the very right), which is a multi line cell. When before pasting the updated data source was fine, even though it has always been multi line, now all of the sudden data from comments column cell is split into multiple lines invading the rows below. I have multiple links to that data source smart sheet therefore it is a problem. Also trying different workaround, if I individually copy that column in, Comments remain in the same cell, but when copied as a full sheet it does not...I am getting very frustrated with smartsheet as I think something as simple as copy pasting information should be much simpler...

  • @David Sanchez Capdevila Did you find a workaround?


    I found that uploading from Excel back into smartsheet, instead of copying from Excel and pasting into smartsheet, allowed the multi-line cells to come into Smartsheet with cell content intact, without invading the rows below.

    This didn't completely solve my problem, however.

    The line breakout still occurred when copying from the temporary upload sheet to the sheet where I need it.

    To get these new verbose cells where I wanted, I had to do a vlookup, which then makes that cell uneditable in the destination sheet, and complicates things a bit.

    I may play around with using linked cells, I just wanted to see if you came up with a workaround. Let me know what you know!

  • My upload sheet was not copy-pasting properly either so I was able to make it work this way:

    -Upload new data from excel in the same format as master sheet

    -In the master sheet, under your existing data, right click and choose "Link from cell in other sheet" and select the whole column in the upload sheet. All of the cells in the column will now be linked over into new rows on the master sheet.

    -Do the same for each column in the upload sheet (*you can select multiple columns but the max number of links you can create at one time is 500 so I had to do this column by column*)

    -Once all linked and looking good, select all and right click, and choose "remove link". The links convert to plain text.

  • HBL
    HBL ✭✭

    I have found a work around after looking for some time to this issue.

    When Pasting into smartsheets it will take Cells in Excel that have line breaks where a cell has multiple lines split it to the cell underneath in smartsheets.

    In excel before copying you need to replace all the linebreaks doing Control+H and in the find input press "Control+J" and replace with either " , " (Comma) or a " " (Blank Space).


    Then when you paste the whole dataset into Smartsheets, it will not split the cell from Excel into two cells.

    Smartsheet Technicians, please resolve this so we do not need to remember a work around please....

  • Hila
    Hila ✭✭

    Additional solution using Excel formulas:

    • Find the line brake using the Excel formula:

    =FIND(CHAR(10),A2)

    • Replace the line brake character with comma using the Excel formula:

    =SUBSTITUTE(A2,CHAR(10),",")

    Then when you paste the whole text into Smartsheet, it will not split the cell from Excel into two cell.

  • Vidura
    Vidura ✭✭

    I am having this same issue. The suggestion to double click into a cell isn't great...as I have about 250 cells that are having this issue.

    When I Import from the Excel, it works fine...but then when I copy/paste from the new sheet back into my target sheet...same issue. So, the issue is that you simply can't paste line breaks...which doesn't make sense because in Excel you can paste these all over the place.

  • Vidura
    Vidura ✭✭

    this did not work for me. It did the find and replace in Excel, but this did not resolve the pasting issue in Smartsheet.

  • Vidura
    Vidura ✭✭

    my apologies but this doesn't make sense to me. How would I do this while within the cells that I need to eliminate the line breaks from?