Results 1 to 5 of 5

Excel 2007 : How to split data in a cell at every 16th character

Threaded View

  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    5

    How to split data in a cell at every 16th character

    I have a range of data in column d in in a spreadsheet. The length if the data in each column varies but i would like to split the data at every 16th character. I know i can do the via the text to columns function but i would prefer to run a macro to do this as i can have in excess of 3000 rows and some rows have more than 600 characters.

    A sample of a row is below:

    DRSPROF CLIEPROF 12111 DUNCPROF 12114 ARISPROF

    What i would like is for the split to occur before the "C" in CLIEPROF, before the "D" in DUNCPROF and before the "A" in ARISPROF. So for the above string there is 56 characters and i would like to split it every 16th character. Another row might have 128 character but i would still like to split every 16th character. Can anyone suggest how i have do this?

    I have tried the split function with the code below but i don't know how to fill the array with the text 1-16 and so on etc....
    For Each x In Range("d2:d3")
    Length = Len(x)
    numberofarrays = Length / 16
    splittext = Split(x)
        For i = 0 To numberofarrays
            If x <> 0 Then x.Offset(0, i + 1) = splittext(i)
        Next i
    Next x
    Thanks Ian
    Last edited by arlu1201; 04-24-2012 at 08:17 AM. Reason: Please put code tags in future.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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