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

makiwara

Board Regular
Joined
Mar 8, 2018
Messages
171
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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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"
 
Last edited:
Upvote 0
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.
gGEI4Ra
Do you know how to solve this by VBA? Have a very nice day! And thanks for helping! :)

gGEI4Ra.jpg

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"
 
Upvote 0
Assuming your text is in cell A1 and the output will start in cell A5, give this macro a try...
Code:
[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]
 
Last edited:
Upvote 0
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...
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top