Text to Columns

[cob]

Hi

I have a small problem, I am trying to write a micro code to split text which is copied into cell A1 into columns. I can do this fine by going to "data" the "text to Columns" and selecting the places i want to split the text (this is the same for every piece of data i copy in). The macro works perfectly every time. the problem is that the spreadsheet is shared and i want to protect certain cells on the sheet, when i protect the sheet the recorded macro does not work as the "data", "text to columns" is not available in a protected workbook.

I was just wondering if someone could help me, so i can run a macro to split the text which also allows me to protect cells. In the "text to column" option the "fixed width" (column breaks) i choose are: 4, 25, 34 and 43.

Any help would be much appreciated!

[mrice]

This line of code should do it.

Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(25, 1), Array(34, 1), Array(43, 1))

[cob]

I'm sorry i'm new to excel code. How do i add the code and once this is done how do i run it!

Thanks!

[mrice]

Hit alt F11

Insert - Module

Paste the following into the new sheet.

Sub MySplit
Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(25, 1), Array(34, 1), Array(43, 1))
End Sub

Hit F11

Tolls-Macros-MySplit

[cob]

The code works perfectly. Thanks very much. However to run it i have to go into 'tools' then 'macro' and hit run there, is there a way to assign a key command to the script, for example Ctrl + T.

It also does not work when lock certain cells and I protect the sheet, is there a way around this?

[mrice]

Go to Tools - macro - select the macro and press options to specify the shortcut key you wish to assign.

[dusit_t]

Hi,

I am searching for fixed width text to column macro and found this reply. However, when I apply above macro, the result of zeros in front of figures disappear.

e.g. sample text to split to column: 000122042009ABCDEFG00567

Required result:
0001|22042009|ABCDEFG|00567


when running below macro; result shows:
1|22042009|ABCDEFG|567

Sub MySplitB()
Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1), Array(19, 1), Array(24, 1))
End Sub

Would anyone please help modify the macro to define result in some columns as text format?

Thanks
DT.

[mrice]

Try replacing 1 with 2 in the appropriate array.

e.g.

Array(0, 2)

to make the first column text,