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

**kidkool** · 04-24-2012, 08:02 AM

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

Please Login or Register  to view this content.

Thanks Ian

**arlu1201** · 04-24-2012, 08:20 AM

DRSPROF CLIEPROF 12111 DUNCPROF 12114 ARISPROF

The 16th characters are highighted. I have included the spaces in the count. But your calculation of the 16th character doesnt match with mine. How is it so?

**tom1977** · 04-24-2012, 08:25 AM

Hi check such code and tell me is it ok?

Please Login or Register  to view this content.

**kidkool** · 04-24-2012, 08:49 AM

Thanks Tom. This is exactly what i was looking for and hadn't even thought of using the MID function. I commented out the line - Columns("b:c").Clear as i have other data in these columns and amended line - For Each c In Columns(1).SpecialCells(2) to - For Each c In Columns(4).SpecialCells(2) as my data is in column D.

Your help is much appreciated.

arlu1201 - it looks like the spaces in between the text hasn;t copied over. Thanks for your time.

**Marcol** · 04-24-2012, 09:37 AM

A way without using VBa.
With your data in Column A beginning Row 2 down
In B2

Please Login or Register  to view this content.

Drag Down as required
In C2

Please Login or Register  to view this content.

Drag across as required, then Down

Are you sure your data splits every 16 characters?
I think you might have cumulative errors on longer strings.

[Edit] Okay, this should work.
When pasting directly to the forum, strings are automatically trimmed of what it considers "extra" spaces.
If you put your sample string in code tags, these spaces will not be trimmed.

See this workbook