Split cell contents

[Johnatha]

Hi everyone,

I have several cells with a various amount of 8-digit numbers in them (see below for example). Some cells have anywhere between zero to twenty 8-digit numbers in them. How can I split up these cells, so that it pulls each 8-digit number and puts it into its own cell?


10000000
10000008
21546587
68555898
10035288



I know I can use text-to-columns with a fixed width, but this would take forever. VBA code perhaps?

Thanks!!

[Bob Phillips]

Use Data>Text To Columns with a delimiter of whatever separates those numbers.

[YasserKhalil]

Here's the link of your thread
http://www.excelforum.com/excel-programming-vba-macros/1048152-split-cell-contents.html

[YasserKhalil]

Here's a little change to get the results in adjacent cells

Private Sub CommandButton1_Click()
'code by xlbiznes
'variables
Dim lrow As Integer
Dim x As Integer
Dim y As Double
Dim a, b As Integer

lrow = Range("a1").Rows.End(xlDown).Row 'get the last row, assuming your data starts from row 1

For x = 1 To lrow
var_string = Range("a" & x) 'pass the data in the cell

y = Application.WorksheetFunction.Ceiling(CDbl(Len(var_string)) / 8, 1) + 1 'check for how many 8 digit numbers exist
b = 0
For a = 2 To y
Cells(x, a) = "'" & Mid(var_string, (b * 8) + 1, 8) 'extract 8 digits from cell contents
b = b + 1
Next

Next

MsgBox "Process Completed", vbInformation, "Xlbiznes"
End Sub