Can I Paste One Line of Text into Multiple Cells?

[jdanniel]

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.

[navic]

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

[Logit]

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.

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

[jdanniel]

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.

[jdanniel]

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?

[Logit]

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

???

[jdanniel]

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.

[Logit]

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

[Logit]

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



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

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

[jdanniel]

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.