Split cell contents

**Johnatha** · 11-06-2014, 03:39 PM

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

**xlbiznes** · 11-06-2014, 03:53 PM

Hi,

try this solution.

**Johnatha** · 11-06-2014, 03:58 PM

Hey xlbiznes!

That doesn't work for my project, the thing is each cell looks like this:

xxxx
xxxx
xxxx
xxxx

So the problem is that the delimiter as a space won't work, since there are no spaces.

Any thoughts? Thanks for the code though, this is exactly how I want to approach the project.

**JOHN H. DAVIS** · 11-06-2014, 04:22 PM

Maybe:

Sub Johnatha()
Dim x As Long
Dim y As Long
Dim i As Long
Dim z As Long
Dim r As Long

For r = 1 To Range("A" & Rows.Count).End(3).Row
y = Len(Range("A" & r))
x = y / 8
z = 8
For i = 2 To x
    Cells(r, i).Formula = "=RIGHT(LEFT(" & Cells(r, 1).Address & "," & z & "),8)"
    Cells(r, i).Value = Cells(r, i).Value
    z = z + 9
Next i
Next r
End Sub

**xlbiznes** · 11-06-2014, 04:25 PM

here is the updated solution.

Split cell contents

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Split cell contents

Re: Split cell contents

Re: Split cell contents

Re: Split cell contents

Re: Split cell contents

Thread Information

Users Browsing this Thread

Similar Threads

Split up contents of cell

[SOLVED] split the cell contents

Split cell contents over multiple rows based on cell contents

split cell contents

Split contents of a cell

Bookmarks

Bookmarks

Posting Permissions