VBA code for Text to Columns

[Jasa P]

I'm looking for VBA code that present the function of Text to Columns. So, if I paste the combination below, it will be separated automatically

M01=09#214.218x20.50/81x50/23.49.78x20 in Cell A1

so in cell
B1 --> M01
C1 --> 09
D1 --> 214.218x20.50/81x50/23.49.78x20

I've tried Record Macro, but it still need to use shortcut key or click Run Macros.

I really need it will be separated automatically after I paste in Workbook.
The numbers are not always the same like
M02=15#22.33.44.55x10.20.30.40/1234.5678x10/50x50
M03=22#1.2.3x1.2.3/10.20.30.40.50x15
M04=10#10x20/21.22.23.24.25.26x5.10/456.678x10/65.64x5/88.99x99x88
M05=05#1010.2020x10.20

and I'm using Excel 2007

Any help would be great
Regards


Jasa

[mdmackillop]

Welcome to VBAX
This goes in the Worksheet module

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim x
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
With Target
x = Split(Target, "=")
.Offset(, 1) = x(0)
x = Split(x(1), "#")
.Offset(, 2).NumberFormat = "00"
.Offset(, 2) = x(0)
.Offset(, 3) = x(1)
End With
Exits:
Application.EnableEvents = True
End Sub
[/vba]