On Wed, 26 Dec 2007 19:57:00 -0800, Jeff
wrote:

Hi Ron, thanks for your response

I tried your method below and the output as follow
A B C D E F
1 1234 23244 121 1442 121223 12
4434 534
2
3

Where cell B2 and D2 still merge the numbers.

Rgds..Jeff

OK, this should work, now that I understand the data to be split is all in one
cell.

I didn't format the destination cell in this version, but that can be easily
added depending on whether you want the values to be text or numeric.

You can still use the Data/Text to columns wizard by specifying the delimiters
as being <space and <other. In the <other box, hold down <alt while you
type 010 on the NUMERIC KEYPAD (not on the numbers at the top of the keyboard).

If that doesn't work, (and it might if there is something else funny about the
data), you can try the sub below:

====================================
Option Explicit
Sub Rearrange()
Dim c As Range
Dim re As Object, mc As Object
Dim i As Long
Const sPat As String = "\w+"

Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = sPat
End With

For Each c In Selection
If re.test(c.Text) = True Then
Set mc = re.Execute(c.Text)
For i = 0 To mc.Count - 1
c.Offset(0, i).Value = mc(i)
Next i
End If
Next c
End Sub
===================================


--ron