Split Numbers In Single Cell Across Columns

Re: Extract multiple numbers from a cell separated by spaces

To do it manualy:
Select A1
Data> Text to Column> Delimited > Space
Then
Copy A1:C1
Edit > Paste Special > Transpose
Is it enough?

Re: Extract multiple numbers from a cell separated by spaces

Try this code... it will make a column out of a the contents of the currently selected cell (assuming the contents of the currently selected cell are seperated by a space).

Option Base 1
Public Sub split_down()
Dim x As Variant


If Selection.Areas.Count > 1 Or Selection.Rows.Count > 1 Or Selection.Columns.Count > 1 Then Exit Sub
x = Application.Transpose(Split(Selection.Value, " "))
Selection.Resize(UBound(x)) = x


End Sub

Regards,
Ger

Re: Split Numbers In Single Cell Across Columns

This seems to work:

Option Base 1
Public Sub split_down()
Dim x As Variant
Dim my_range As Range
Dim my_cell As Range


Set my_range = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Range("$B:$C").ClearContents


For Each my_cell In my_range
    x = Application.Transpose(Split(my_cell.Value, " "))
    Range("c" & Rows.Count).End(xlUp).Offset(2, 0).Resize(UBound(x)) = x
Next


Range("$C:$C").SpecialCells(xlCellTypeConstants).Offset(, -1).Formula = "=""A"" & COUNTA($A:$A)-COUNTBLANK(C3:C$" & Range("C" & Rows.Count).End(xlUp).Row & ")"


End Sub

Have a good weekend.
Ger

Re: Split Numbers In Single Cell Across Columns

LOL ::D

Thanks, I'm on a lucky streak today.

Cheers
Ger

Re: Split Numbers In Single Cell Across Columns

Hi John, its not clear to me what problem you are having with the ID's. See attached, where the ID's are fine. I update the code to show the data starting on Row 1. I also removed the formulas.

Option Base 1
Public Sub split_down()
Dim x As Variant
Dim my_range As Range
Dim my_cell As Range


Set my_range = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Range("$B:$C").ClearContents


For Each my_cell In my_range
    x = Application.Transpose(Split(my_cell.Value, " "))
    Range("c" & Rows.Count).End(xlUp).Offset(2, 0).Resize(UBound(x)) = x
Next


Range("$C:$C").SpecialCells(xlCellTypeConstants).Offset(, -1).Formula = "=""A"" & COUNTA($A:$A)-COUNTBLANK(C3:C$" & Range("C" & Rows.Count).End(xlUp).Row & ")"
Columns(2).Copy: Columns(2).PasteSpecial Paste:=xlPasteValues: Application.CutCopyMode = False
Range("B1:C2").Delete shift:=xlUp


End Sub

Ger

Re: Split Numbers In Single Cell Across Columns

@comments to code

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
is probably equal to
[COLOR="royalblue"]Range("A1").currentregion.columns(1)[/COLOR]
or
[COLOR="royalblue"]usedrange.columns(1)[/COLOR]

Range("$B:$C").ClearContents is unnecessary in VBA, because Range("B:C") is always fixed.

Split(my_cell.Value, " ")) can be replaced by Split(my_cell.Value) because space is the default delimiter

the loop can be rewritten as

For Each cl In my_range
    cells( Rows.Count,3).End(xlUp).Offset(2).Resize(UBound(split(cl))) = Application.Transpose(Split(cl))
Next

Re: Split Numbers In Single Cell Across Columns

Hi SNB

@comments to @comments to code ::D

Different way of saying the same story with no tangible imrpovement on performance IMHO.

In fact, I dont like this -

cells( Rows.Count,3).End(xlUp).Offset(2).Resize(UBound(split(cl))) = Application.Transpose(Split(cl))

, because you are splitting the array twice, whereas I only split it once. If it was large array, it would be a performance hit against your code.

And as for split with the default value - its risky when dealing with different office versions (03/07/10) in different regions (US/UK/FR/DE). By force of habbit I NEVER rely in defaults with Excel. It's no extra weight to carry for that extra bit of insurance. Just my tupence worth anyway

Cheers
Ger

Re: Split Numbers In Single Cell Across Columns

I have in cell (A1) "001 - 002, 004 - 006, 008" (These are separated by a space)
and insert entire row and paste in column wise.

Input:
A1 Cells "001 - 002, 004 - 006, 008"

Output:
Cells insert and paste below data

A1 Cells 001
A2 Cells 002
A3 Cells 004
A4 Cells 005
A5 Cells 006
A6 Cells 008

Re: Split Numbers In Single Cell Across Columns

MaheshLove

Please open a new thread for your own question.