Text-To-Columns would not be able to handle your data as it is possible for
there to be no space between your numbers and your text. Give this macro a
try (change the assignments in the two Const statements to reflect your
actual conditions)...

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Word As String
Dim Number As String
Const StartRow As Long = 2
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = StartRow To LastRow
With .Cells(X, "A")
Number = ""
Word = ""
For Z = 1 To Len(.Value)
If Mid(.Value, Z, 1) Like "[0-9.]" Then
Number = Number & Mid(.Value, Z, 1)
Else
Word = Word & Mid(.Value, Z, 1)
End If
If Left(.Value, 1) Like "[0-9.]" Then
.Offset(, 1).Value = Number
.Offset(, 2).Value = Trim(Word)
Else
.Offset(, 1).Value = Trim(Word)
.Offset(, 2).Value = Number
End If
Next
End With
Next
End With
End Sub

--
Rick (MVP - Excel)


"danpt" wrote in message
...
I need a vba code to do what is similar to text-to-columns, to separate
numerals and alphabets.
Thanks

column(A) column(B) column(C)
2.85 AUD 2.85 AUD
2.85AUD 2.85 AUD
AUD 2.85 AUD 2.85
AUD2.85 AUD 2.85
20090315 data 20090315 data
20090315data 20090315 data
data 090315 data 090315
data090315 data 090315