Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Can I Paste One Line of Text into Multiple Cells?

  1. #1
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016

    Can I Paste One Line of Text into Multiple Cells?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, everyone, I'm using Office 365, so I'm assuming I have the most current version of Excel that's available.

    I have a question about pasting text into multiple columns' cells. This might sound like the pinnacle of laziness, but it really will help streamline my data entry.

    My movie inventory spreadsheet contains five adjacent columns for actors. If I need to enter only one or two names, I usually just type the names in the columns. But if I need to enter names into all five actors' columns, then I copy the names from IMDB into Notepad. Then, I paste the names into the columns, one by one, selecting them individually.

    (The reason I use Notepad as the middle man here is to avoid having to use the format painter. If I accidentally click an actor's name, or director's name, or movie's name when using the format painter, it opens up IMDB to that name. This slows me down, so by using plain ASCII text via Notepad, I avoid this.)

    What I want to do, if possible, is find a way to select all of the names in Notepad, select the cells in Excel, and paste the names into those cells, all at once, in one fell swoop, as it's called.

    Is this possible? If it is possible, what's the procedure, please? My guess is if it is possible, I'd have to have one line of text, separated by delimiters. Am I right?
    Thank you! Jack D.
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by jdanniel View Post
    Is this possible? If it is possible, what's the procedure, please? My guess is if it is possible, I'd have to have one line of text, separated by delimiters. Am I right?
    If I understand what you want, perhaps

    1. In Notepad you have, one line text with 10 names separated with delimiter like semicolon (; )
    2. Copy from Notepad to Excel in A1
    3. Data Tab -> Text to Columns (Now, you have 10 names in columns)
    4. Select all filled cells in row
    5. Copy
    6. Select some cell
    7. Paste Special -> Transpose
    8. Now, all names are placed in 10 rows
    9. Copy to your destination Wbk

    btw: Record VBA macro and use them when you need
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    221
    Articles
    0
    Excel Version
    2007
    Another approach :

    I searched the IMDB.com website for Attack of the Killer Tomatoes. Using this url : https://www.imdb.com/title/tt0080391/?ref_=fn_al_tt_1 you can see a list of three actors towards the top of the page.
    They list as : Stars: David Miller, George Wilson, Sharon Taylor

    Highlight the three names and copy.

    Go to the MAIN SHEET in your workbook and paste the three names in Column I for the corresponding film. In this case that would be row #2 ... so paste in I2 .

    Then click the FORMAT button provided in your revised workbook (attached) and the macro will do the rest.

    Code:
    Option Explicit
    
    
    Sub FormatActors()
    
    
        Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
            
        With Columns("I:M")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Font.Name = "Arial"
            .Font.Size = 12
        End With
        
        
    End Sub
    Attached Files Attached Files

  4. #4
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    Let's say I have three names in Notepad, and I have formatted the line like this:

    Test Name 1; Test Name 2; Test Name 3

    I get lost at your Step # 4. You say "Select all filled cells in row." I don't really understand what you mean by that. Can you elaborate in a bit more detail, please? Also, when you say "Select some cell," I don't know what you mean by that. Can you please elaborate on that a bit more? Thank you.

  5. #5
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    The problem with using IMDB is I may not necessarily want to use those names in that particular order, which means I have to copy names to Notepad in the order I choose. Would that change anything?

  6. #6
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    221
    Articles
    0
    Excel Version
    2007
    .
    I had not experimented with different methods of transferring the actor names to your workbook.

    I did just type a few names into Notepad (also tried with WordPad), each name separated by a comma. Then pasted the names into I2 fully
    expecting the need to run the macro. To my surprise ... so long as the names are separated by a comma, they populate to their own column
    without having to run the macro or do anything else.

    If you are wanting to arrange the actor names in a different order than what is displayed on IMDB.com ... I guess the above is the only way
    you could do without running the macro which I believe takes you back to where you began ... with your original method ?

    Other than personal preference .. is there a particular reason you want to have the names arranged in a specific order ? It appears that
    IMDB lists the actor/s pretty much in order of their significance in the movie / their value/worth as an actor. I understand we are looking
    at the names importance in your workbook from different perspectives but ... I would be just as happy using the method outlined in
    my previous post. If anyone wanted additional information they could as easily research IMDB as anyone else.

    ???

  7. #7
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    Thank you for following up. I have not made any progress with this at all, so let me tell you what I tried doing.

    First of all, I'm not sure I understand why you are using cell I2. At the time of this writing, I'm on line 1339. Can you please explain why you used I2?

    Now...I also typed a few test names into Notepad. Three, to be precise. I put them all in one line, separated by commas. Such as this:

    Al Pacino, Marlon Brando, James Caan

    I tried pasting this into cell I1339. The entire line ended up in I1339. They did not populate into their own columns. But yours did. Why? Why yours and not mine?

    Next, I tried doing something different. I gave each name their own line, ending with a comma, such as this:

    Al Pacino,
    Marlon Brando,
    James Caan

    When I tried pasting this text into I1339, the names did paste into their own individual cells, but not horizontally. In other words, I wanted them to populate I1339, J1339, and K1339.

    They did not. They populated I1339, 11340, and 11341.

    So, I don't know what you did that I was unable to do.

    As for the order I want the names arranged...the only reason I choose my own order is personal preference. Unfortunately, my own personal preference may not be the order IMDB puts their actors. But this, to me, is irrelevant, because I can't seem to do what I want to do, while you can. That's what has me stumped.

  8. #8
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    221
    Articles
    0
    Excel Version
    2007
    .
    I tried experimenting with other rows than Row #2. I find the need to use the macro to cause the three actor names to move to their own cells.

    I can't explain why it automatically worked when I pasted the names in Row #2 (I2). For some reason, when I did it this time, it did not auto populate to the three
    different columns. I had to use the macro. Go figure ... The wonders of Excel and VBA ?

    My testing this time was with rows 1216, 1217, 1218, etc. etc. It required the use of the macro.

    I was hoping I could save you some steps and time with the macro, if you are satisfied with pasting just the three actor names.

    If you go to IMDB.com and click on " See full cast & crew " , then attempt to copy the top five actor names in the vertical list,
    it ends up copying their images and character names as well. I suppose a macro could be formulated to remove the extraneous data
    to provide only the five actor names but .... ???

  9. #9
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    221
    Articles
    0
    Excel Version
    2007
    .
    I've cobbled together some code that will process all five actors and automatically paste to the first blank row on the MAIN SHEET.
    The attached workbook has one additional sheet named TEMP. This is the sheet you will paste the copied data to, in cell A1. Then
    click the PROCESS button and the macro will take care of the rest.

    All you need do after that is to fill in the cells from Col A : H.

    (I'm certain there is a better means / code of accomplishing the goal but this code works successfully).


    When copying the five actor names from IMDB.com (here is an example you can follow) :


    Go to IMDB.COM and search for the movie HIGH LIFE.


    Click on " See full cast & crew "


    Highlight the first five actors, to include their images.


    Copy the selection.


    Paste to Sheet TEMP in cell A1.


    Click the PROCESS button.


    When copying the selection from IMDB.com, you will copy what is viewed in this image :

    Click image for larger version. 

Name:	IMDB Copy.jpg 
Views:	12 
Size:	32.6 KB 
ID:	9040

    Note the area / items that are highlighted in blue color.


    Code:
    
    Sub ProcessTemp()
    
    
    ActiveSheet.Pictures.Delete
    
    
        Columns("A:A", "C:D").Delete
        'Columns("A:A").Delete
        ''Exit Sub
        Range("A3").Cut
        Range("B2").Select
        ActiveSheet.Paste
        Range("A4").Cut
        Range("C2").Select
        ActiveSheet.Paste
        Range("A5").Cut
        Range("D2").Select
        ActiveSheet.Paste
        Range("A6").Cut
        Range("E2").Select
        ActiveSheet.Paste
        Range("A2:E2").Hyperlinks.Delete
        
        UpdateSheet1
        
    End Sub
    
    
    Sub UpdateSheet1()
    Dim ws2 As Worksheet, ws1 As Worksheet
    Dim MyRng As Range, cell As Range
    Dim vFind As Range
    Set ws1 = ThisWorkbook.Sheets("Temp")         'edit this sheet name as needed
    Set ws2 = ThisWorkbook.Sheets("Main")         'edit this sheet name as needed
    
    
    Set MyRng = ws2.Range("I" & Rows.Count).End(xlUp).Rows
    
    
    Application.ScreenUpdating = False
    On Error Resume Next
    
    
    For Each cell In MyRng
        Set vFind = ws2.Range("I:I").Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not vFind Is Nothing Then
            ws1.Range("A2:G2").Copy
            ws2.Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            ws2.Activate
            ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
            ws1.Range("A2:G2").Value = ""
        
        End If
    Next cell
    Application.ScreenUpdating = True
    
    
    FormatActors
    
    
    End Sub
    
    
    Sub FormatActors()
            
        With Columns("I:M")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Font.Name = "Arial"
            .Font.Size = 12
        End With
        
    End Sub
    Attached Files Attached Files

  10. #10
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    Thank you for replying again, and for the code. Will this code work if I don't have five names? For example, let's say I have only two or three.

Page 1 of 2 1 2 LastLast

Posting Permissions

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