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
Bookmarks