VBA // How to Insert a TXT file to Excel, if I want to get every word in a different cell

[makiwara]

Hi!

I would like to insert a TXT file to EXCEL using VBA code. The columns 1-10 would contain the data, and then new row would start.

For example:

[FONT=founders_grotesk_textlight]Ella Higginson was born around 1862 in Council Grove, Kansas. A poet as well as a novelist, short story writer, and nonfiction writer, Higginson was the author of the poetry collections [/FONT]The Vanishing Race [FONT=founders_grotesk_textlight](C. M. Sherman, 1911), [/FONT]The Voice of April-Land and Other Poems [FONT=founders_grotesk_textlight](The Macmillan Company, 1903), [/FONT]Four-Leaf Clover.

1 2 3 4 5 6 7 8 9 10 (a,b,c,d,e,f,g,h,j,k)
Ella Higginson was born around 1862 in Council Grove, Kansas.
A poet as well as a novelist, short story writer, and
nonfiction writer, ... ... ...

So there would be in every line 10 words, then the next row...
Until the TXT is over. (TXT name is "Temp" , but if its better, it could be a Temp named Word docx file too.)

Space between the words is a great indicator for splitting the text.

Thank you four your help, it means to me a lot! Have a very nice day!

 

[Rick Rothstein]

How would this part of your text look when split apart (put each "word" on a separate line so we can tell what goes in a cell... it is hard to tell from your original post what goes in what cell)...

"Race (C. M. Sherman, 1911), The"

 

[makiwara]

Hi Rick, I hope that this will help you. So there will be for example 10 columns and it starts to insert the words from a txt at A1-J1, then at A2-J2 and so on.

Do you know how to solve this by VBA? Have a very nice day! And thanks for helping!

How would this part of your text look when split apart (put each "word" on a separate line so we can tell what goes in a cell... it is hard to tell from your original post what goes in what cell)...

"Race (C. M. Sherman, 1911), The"

 

[Rick Rothstein]

Assuming your text is in cell A1 and the output will start in cell A5, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub OneWordPerCellTenCellsPerRow()
  Dim X As Long, Arr As Variant, Result As Variant
  Range("A5").CurrentRegion.ClearContents
  Arr = Split(Replace(Range("A1"), Chr(160), " "))
  ReDim Preserve Arr(1 To UBound(Arr) + 10)
  For X = 0 To UBound(Arr) - 10 Step 10
    Cells(5 + X / 10, "A").Resize(, 10) = Application.Index(Arr, , Array(1 + X, 2 + X, 3 + X, 4 + X, 5 + X, 6 + X, 7 + X, 8 + X, 9 + X, 10 + X))
  Next
End Sub[/td]
[/tr]
[/table]

 

[makiwara]

Dear Rick, as I have mentioned the text would come from a TXT file on my desktop.
A cell has unforutantely a max limit of 1024 words.

Can you please modify the code to read a txt file (named TEMP.txt on my desktop, but it' not relevant)

And thank you for your help, you really don't know how much it means to me! Have a very nice day!

Assuming your text is in cell A1 and the output will start in cell A5, give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub OneWordPerCellTenCellsPerRow()
  Dim X As Long, Arr As Variant, Result As Variant
  Range("A5").CurrentRegion.ClearContents
  Arr = Split(Replace(Range("A1"), Chr(160), " "))
  ReDim Preserve Arr(1 To UBound(Arr) + 10)
  For X = 0 To UBound(Arr) - 10 Step 10
    Cells(5 + X / 10, "A").Resize(, 10) = Application.Index(Arr, , Array(1 + X, 2 + X, 3 + X, 4 + X, 5 + X, 6 + X, 7 + X, 8 + X, 9 + X, 10 + X))
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]