Import textfile to excel and split (spaces) to column !

[Petter120]

Hi

Is it possible to import a textfile whit VBA to excel and split every (space) to a column) ?

Textfile
Hello how old are you ? i am 22 years old
Please help me im getting nuts

Excel
[ ] = Columns
[A]Hello [B]how [C]old [D]are [E]you [F]? [G]i [H]am [I]22 [J]years [K]old
[A]Please [B]help [C]me [D]im [E]getting [F]nuts

I think you get the idea.

Best regards

Petter

[Russell Dawson]

Yes, use the "Text to columns" facility - using data type as delimited with spaces separating each field.

[Petter120]

Sorry i forgott to say, can it be done whit VBA ?

[AlvaroSiza]

Petter,

Turn on your macro recorder and, From the Data tab, select 'From Text File'. Here was the result on my side, which includes what appear to be static arrays near the bottom.

Please Login or Register  to view this content.

[Russell Dawson]

If you need help with doing this the following should help.


In 2007
Paste data into A1 < Col A should be highlighted
Select Data tab
In Data Tools select Text to columns
Make “delimited” the radio button selection
Next
Deselect all except for space as delimiter
Next
Each should now be separated
Format each column as required (text in this case no doubt)

[baldphil]

You can also use the "Split" function to get a string array, as in:

Please Login or Register  to view this content.

This may create problems if you have multiple spaces between words, however.

[baldphil]

I forgot to include instructions on how to get the textfile as a string. Here you go:

Please Login or Register  to view this content.

You need to have a reference set to "Microsoft Scripting Runtime" (or browse for Scrrun.dll) in Tools -> References

[Petter120]

thanks it works great