Excel 2007 : How to split to column at character

**PBMClubber** · 11-22-2010, 04:34 AM

Hello,

Is it possible to split this long string to columns? The string contains 99 characters and needs to be split at character 1, 2, 16, 17, 26, 32, 42, 47, 51, 52, 53, 59, 63, 73, 77, 89, 98

I did replace all spaces with _, because this forum removes spaces.

UO00028921288365B000000100000340000223300099998____Y2NOT_COAKMM__________________________000000000_

Many thanks for all help.

**DonkeyOte** · 11-22-2010, 04:37 AM

Have you tried using Data Tab -> Text to Columns -> Fixed Width ? [applying the breaks appropriately]
(record yourself doing this if you wish for a VBA solution)

**PBMClubber** · 11-22-2010, 05:02 AM

Im trying to create message converter, to make the code readable. I would just like to enter the code in one cell which then automatically splits it to columns. I prefer to use VBA code to make this to work. I appreciate all help to make it to work.

**teylyn** · 11-22-2010, 05:25 AM

- turn on the macro recorder
- execute Data > Text to Columns > Fixed Width -- as DonkeyOte suggested
- transfer the recorded code to a Change Event that monitors the cells where the data is entered or to a standard module and execute on demand.

**PBMClubber** · 11-22-2010, 06:21 AM

The macro did work fine thanks for the advise.
Now i noticed that the output gets too wide and is not friendly to read.
Is it easy to make everything in rows?

Like this:

1 Packet type:U
1 Message type:O
14 Order token:00028921288365
1 Buy/Sell:B
9 Quantity:000000100
6 Order book:000340
10 Price:0002233000
5 Time in force:99998
4 Firm:
1 Display:Y
1 Capacity:2
6 User:NOT_CO
4 Client reference:AKMM
10 Order reference:
4 Clearing Firm:
12 Clearing account:
9 Minimum quantity:000000000
1 Cross type:

**teylyn** · 11-22-2010, 06:34 AM

Please post an Excel workbook with your data before and after the manipulation, so members here can see exactly what your input and your expected output is.

**PBMClubber** · 11-22-2010, 07:50 AM

I did add .xlsm file to this message. The 99 characters and needs to be split at character 1, 2, 16, 17, 26, 32, 42, 47, 51, 52, 53, 59, 63, 73, 77, 89, 98.