VBA code for Text to Columns

[Jasa P]

Hi, I'm looking for VBA code that can save the function of Text to Columns. So, if I open a workbook and paste the combination below, I don't need to separate them manually using Text to Column. This combination will be separate in few columns automatically after I pasted them in workbook.

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

The numbers are not always the same, can be more complicated than this.
I'm using Excel 2007

Any help would be great
Regards


Jasa

 

[SteveO59L]

Try using the macro recorder

 

[Jasa P]

Try using the macro recorder

So, I just need to click on Record Macro, then click the data --> Text to Columns --> Finish --> Stop recording

Then, If I paste other combination in cell A2, it will be separated automatically?

 

[SteveO59L]

Post the code that you recorded

 

[Jasa P]

Post the code that you recorded

Sub TTC()
'
' TTC Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(3, 9), Array(4, 1), Array(6, 9), Array(7, 1)), _
        TrailingMinusNumbers:=True
End Sub

Thanks for helping me, I don't really understand about Macro or even VBA.

Regards,


Jasa

 

[SteveO59L]

That should perform the text to columns thingy for all entries in col A.

Where you may have problems, is if subsequent entries dont follow the same format

 

[Jasa P]

That should perform the text to columns thingy for all entries in col A.

Where you may have problems, is if subsequent entries dont follow the same format

Hi, please let me give some examples to make it clear: (in column A)
1. M01=09#214.218x20.50/81x50/23.49.78x20
2. M02=15#22.33.44.55x10.20.30.40/1234.5678x10/50x50
3. M03=22#1.2.3x1.2.3/10.20.30.40.50x15
4. M04=10#10x20/21.22.23.24.25.26x5.10/456.678x10/65.64x5/88.99x99x88
5. M05=05#1010.2020x10.20

the result are:
1. cell B --> M01
cell C --> 09
cell D --> 214.218x20.50/81x50/23.49.78x20

2. cell B --> M02
cell C --> 15
cell D --> 22.33.44.55x10.20.30.40/1234.5678x10/50x50

3. cell B --> M03
cell C --> 22
cell D --> 1.2.3x1.2.3/10.20.30.40.50x15

4. cell B --> M04
cell C --> 10
cell D --> 10x20/21.22.23.24.25.26x5.10/456.678x10/65.64x5/88.99x99x88

5. cell B --> M05
cell C --> 05
cell D --> 1010.2020x10.20

Thank you

 

[Jasa P]

If I paste a new combination to cell A2 and click the shortcut key, there's a warning
"Do u want to replace the contents of destination cell?"

I click Ok, then data in cell B1 becomes blank. But, the combination in cell A2 is separated well.

Why is this happening? Thank u

Best regards

Jasa