Macro using TextToColumns - how to make it work on any length of the text string

[Ballet4ever]

Hello,
I have recorded a macro that uses Text To Column feature. Right now it only works for short strings of text (109 Arrays?)How do I make it work on any length of the text string? Below is the sample data that needs to be split into columns and the code recorded in the macro:
Data (the result should have each number in a separate column without commas and spaces):
00110216 , 110216 , 00115576 , 0115576 , 115576 , 01453232 , 1453232 , 0441187 , 441187 , 050193 , 50193 , 1000CAC43504 , 1236645 , 1572691 , 1646077C1 , 1646077 , 1646077CL , 2029812C1 , 2029812C91 , 20961030010 , 20961030020 , 20961030232 , 20961030373 , 20961030413 , 20961040312 , 209610012237 , 209610014204 , 209610053628 , 20961030010V , 20961030020V , 20961030413AS , 20961030413A , 2229812C1 , 2501403C1 , 2507276C1 , 25504 , 2711453232 , 3000154C1 , 3265017 , 33001A17680G , 33001A17680 , 33001A18785G , 33001A18785 , 33001E0047610000 , 33001E3486 , 33001E3488G , 33001E3488 , 33001E3491 , 33001E3951G , 33001E4386G , 33001E3951 , 33001E4761 , 33001S5070D , 33001S5070 , 3300IA17680G , 3300IA17680 , 3300IA18785G , 3300IA18785 , 3300IE0047610000 , 3300IE3486 , 3300IE3488G , 3300IE3488 , 3300IE3491 , 3300IE3951G , 3300IE4386G , 3300IE3951 , 3300IE4761 , 3300IS5070D , 3300IS5070 , 347FF1 , 3515528C91 , 3515528C92 , 3515528C93 , 3515528C94 , 3530996C1 , 3E7011 , 441117 , 44IHC8A , 539448041P , 53948041P , 590203 , 611011 , 615CA131 , 69594 , 817116 , 818965 , 89112801 , 89112881 , ABPN0944013504 , ATM81255210TF , BT1131 , BTC1131 , CA1187 , CA2046 , CAC1131 , CAC131 , CAC43504 , CAC611011TRP , CACIN103 , CBNV8A , CNV8A , FLP010707 , FLX010707 , HDH010223BP , HDH010223 , MOD1A17680G , MOD1A17680 , MOD1A18785G , MOD1A18785 , MOD1E0047610000 , MOD1E3486 , MOD1E3488G , MOD1E3488 , MOD1E3491 , MOD1E3951G , MOD1E4386G , MOD1E3951 , MOD1E4761 , MOD1S5070D , MOD1S5070 , MODIA17680G , MODIA17680 , MODIA18785G , MODIA18785 , MODIE0047610000 , MODIE3486 , MODIE3488G , MODIE3488 , MODIE3491 , MODIE3951G , MODIE4386G , MODIE3951 , MODIE4761 , MODIS5070D , MODIS5070 , NAV16404EDV , NAV16404ED , 16404 , NAV16404NV , NAV16404N , NAV16404RV , NAV16404R , NAV16404V , NAV16404 , NV8A , PC2931 , RCCAC43504 , REA611011 , RHT028 , S19886 , SC222046 , 222046 , SCSI222046 , SMR1646077C1 , SPBN0944013504 , SPI44013504 , 44013504 , SRMIHC8ABP , SRMIHC8A , TPL611011 , TIX1030010 , TIX1030020 , TIX1030232 , TIX1030373 , TIX1030413 , TIX1040312 , TIX10012237 , TIX10014204 , TIX10053628 , TIX1030010V , TIX1030020V , TIX1030413AS , TIX1030413A , TXE1030010 , TXE1030020 , TXE1030232 , TXE1030373 , TXE1030413 , TXE1040312 , TXE10012237 , TXE10014204 , TXE10053628 , TXE1030010V , TXE1030020V , TXE1030413AS , TXE1030413A , VAB1030010 , VAB1030020 , VAB1030232 , VAB1030373 , VAB1030413 , VAB1040312 , VAB10012237 , VAB10014204 , VAB10053628 , VAB1030010V , VAB1030020V , VAB1030413AS , VAB1030413A , VAL1030010 , VAL1030020 , VAL1030232 , VAL1030373 , VAL1030413 , VAL1040312 , VAL10012237 , VAL10014204 , VAL10053628 , VAL1030010V , VAL1030020V , VAL1030413AS , VAL1030413A , WSR1131 , ZBSC003 , ZGSC003


CODE:

Sheets("Drupal").Select
Range("A1").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), _
Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array( _
85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), _
Array(92, 1), Array(93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array( _
98, 1), Array(99, 1), Array(100, 1), Array(101, 1), Array(102, 1), Array(103, 1), Array(104 _
, 1), Array(105, 1), Array(106, 1), Array(107, 1), Array(108, 1), Array(109, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

[Eastw00d]

Hi, maybe you can work this out with the split function:

Please Login or Register  to view this content.

Cheers
Erwin

[Artik]

You can also omit the FieldInfo parameter (with the whole array of course)

Please Login or Register  to view this content.

There is a small problem with the Split method. Numeric values will be text in cells.

Artik

[Ballet4ever]

Hello,
Thank you for your suggestion. It works, but there is a problem - it cuts off the last value. In the sample data two last values are "ZBSC003 , ZGSC003". After running the macro, the last value is ZBSC003 and ZGSC003 disappears. Is it because there is no " , " after it? How can I fix it?

[Ballet4ever]

Thank you. I actually need the text values to preserve leading zeros. Can I do it with your method? The method worked well, but my leading zeros have disappeared. When I record the macro and choose Column date format: text (Text to column feature), for some reason there is no code written for this choice in the macro. How can I make sure the macro inserts values as text?

[Artik]

Unfortunately, you would have to define it in parameter FieldInfo.

(...) but there is a problem - it cuts off the last value. In the sample data two last values are "ZBSC003 , ZGSC003". After running the macro, the last value is ZBSC003 and ZGSC003 disappears. (...) How can I fix it?

Take advantage of the Eastw00d proposal. Only change the line:

Please Login or Register  to view this content.

Artik

[Ballet4ever]

Unfortunately, you would have to define it in parameter FieldInfo.


Take advantage of the Eastw00d proposal. Only change the line:

Please Login or Register  to view this content.

Artik

Thank you so much! That worked. However, I need to split multiple cells in the same workbook (on separate worksheets), and when I duplicate the code for each spreadsheet, I get a Compile error: Duplicate declaration in current scope. Is there any way to avoid this? I don't want to create a separate macro for each worksheet, even though that seemed to help avoiding the error.

[Artik]

I get a Compile error: Duplicate declaration in current scope. Is there any way to avoid this?

To answer this question, I need to know the contents of the entire module. One can only guess that in one module there are two procedures with the same name.

Also, some data will end with a comma (see below). Is there any way to trim that comma if it is the very last character in the string?

Is there still space after the last comma? Probably yes, but check it out.

Artik

[Ballet4ever]

To answer this question, I need to know the contents of the entire module. One can only guess that in one module there are two procedures with the same name.


Is there still space after the last comma? Probably yes, but check it out.

Artik

1. Right now there are only two worksheets that have one cell each (cells with long strings of data to be split), so I just created two macros - one for each worksheet. It works for now, and it is not too strenuous to run two macros instead of one. :-) When it becomes strenuous, I will be back.
2. There was no space, but I used =Left (A1,Len(A1)-1) formula to trim the comma before the data gets split. That seemed to work.
Thank you so much for your help! it is greatly appreciated!

[Artik]

Procedure:

Please Login or Register  to view this content.

paste into a standard module (eg Module1). Remove similar procedures from sheet modules. When you open the Macro window (Alt + F8) you should see one routine that does the split - MyTextToColumns. You can start it regardless of whether the first or the second sheet is active. Is required only that cell A1 contains the text to split.

Artik

[Ballet4ever]

Procedure:

Please Login or Register  to view this content.

paste into a standard module (eg Module1). Remove similar procedures from sheet modules. When you open the Macro window (Alt + F8) you should see one routine that does the split - MyTextToColumns. You can start it regardless of whether the first or the second sheet is active. Is required only that cell A1 contains the text to split.

Artik

Great! Thank you.