Split range of cells into individual numbers?

[lambic]

Hi,

I'm writing an Excel / Word mailmerge application to create a set of numbers which will be used for printing labels. I have the code which creates a range based on the start number & quantity required entered into a form (e.g. start at 1, require 100 therfore range of numbers = 1 to 100). However, the labels will need the number splitting up, for example:

Number = 152
Number as required on label = 0 0 0 1 5 2 (each number is to be printed in a box)

I'm not sure how to:

a) Copy the range of numbers that has been generated & convert it to text (so that I can create the leading zeros).
b) Then Split the converted number range (e.g. 000152) into 6 separate cells.

Apologies if the post sounds a bit nonsensical!

Thanks in advance for any ideas!

[Bob Phillips]

You could use this array function



Function SplitData(rng As Range)
Dim FullData As String
Dim ary(1 To 6) As String
Dim i As Long

FullData = Right$("00000" & rng.Value, 6)
For i = 1 To 6

ary(i) = Mid(FullData, i, 1)
Next i

SplitData = ary
End Function