Results 1 to 10 of 14

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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?

    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
    887
    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
    225
    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
    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?

  5. #5
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    225
    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.

    ???

  6. #6
    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.

  7. #7
    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.

  8. #8
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by jdanniel View Post
    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.
    4. Select the filled cell with the names (in your case this is 3 cells with names)
    6. Select a desired cell (eg G7). Here you will transpose to column
    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

  9. #9
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    225
    Articles
    0
    Excel Version
    2007
    .
    Here is a better version of the last macro post. It does a better job of cleaning up after itself.

    Code:
    Sub ProcessTemp()
    
    
    ActiveSheet.Pictures.Delete
    
    
    With ActiveSheet
        
        .Columns("C:D").Delete
        .Columns("A:A").Delete
        .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
    End With
        UpdateSheet1
        
    End Sub

  10. #10
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    My VBA solution
    Code:
    Sub Macro1()
        Range("A1").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1)), TrailingMinusNumbers:=True
        Range("A1:X1").Select
        Selection.Copy
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
    End Sub
    Attached Files Attached Files
    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

Posting Permissions

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