If you're using xl2k or higher, you could use VBA's Split function.

Option Explicit
Sub testme()

Dim myArr As Variant
Dim myCell As Range

Set myCell = Worksheets("sheet1").Range("a1")

myArr = Split(Application.Trim(myCell.Value), " ")

myCell.Offset(0, 1).Resize(UBound(myArr) - LBound(myArr) + 1, 1).Value _
= Application.Transpose(myArr)

End Sub

There are different limits depending on what version of excel you're using.
xl2k has a limit of 5461(?) elements that can be passed to
application.transpose. xl2002+ doesn't have that restriction.

wrote:

Hi,

I have a string of numbers, each separated by a space, in a single
cell. I want to take that string, do a text-to-columns, and then
copy-paste special-transpose into a single column. I tried recording
this macro, but I have too many numbers and it will exceed the 256
column limit when I do text to columns. How can I do this through VBA?

Thanks!

Brett

--

Dave Peterson