+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : How to split to column at character

  1. #1
    Registered User
    Join Date
    11-22-2010
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    25

    How to split to column at character

    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.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How to split to column at character

    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)

  3. #3
    Registered User
    Join Date
    11-22-2010
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to split to column at character

    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.

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: How to split to column at character

    - 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.

  5. #5
    Registered User
    Join Date
    11-22-2010
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to split to column at character

    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:

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: How to split to column at character

    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.

  7. #7
    Registered User
    Join Date
    11-22-2010
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to split to column at character

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1