Split Numbers In Single Cell Across Columns

• I have in cell (A1) 3 7 10 (These are separated by a space)
and in (B1) 2 4 11 23 (These are separated by a space also)

What I need is in Column (B)
(B1) 3
(B2) 7
(B3) 10

In Column (C)
(C1) 2
(C2) 4
(C3) 11
(C4) 23

• 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?

Triumph without peril brings no glory: Just try

• Re: Extract multiple numbers from a cell separated by spaces

It does not get the first number. There is a space before it.

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

Regards,
Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: Split Numbers In Single Cell Across Columns

Great! Almost there.

I have numerous cells (over 50) to separate in Column(A) and put in column (C)
The cell to start on is (A1) and move the results to (C1) then get next data cell in (A2) repeat above.

Skip a line before posting the next results.

It would help if in Column(B) it had a reference number for each separation. Example all seperations from cell(A1) would be identified as "A1" in Column(B)
next separation would be "A2".

This would allow me to use a pivot table on the data.

• Re: Split Numbers In Single Cell Across Columns

This seems to work:

Have a good weekend.
Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: Split Numbers In Single Cell Across Columns

GREAT!

I stand and appluad your expertiese. This is exactly what I need!

Thanks

• Re: Split Numbers In Single Cell Across Columns

LOL ::D

Thanks, I'm on a lucky streak today.

Cheers
Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: Split Numbers In Single Cell Across Columns

Take a look again at the cell ID.

From (A1) It shows A2, then corrects to A1
Next from (A2) shows A0, then shows A1
Next from(A3) shows A2, then corrects to A3

• Re: Split Numbers In Single Cell Across Columns

Also

Would like the new data to start at the top row in Columns (B1) and (C1). It now starts on the row below the last data entry

Thanks,

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

Ger

Files

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: Split Numbers In Single Cell Across Columns

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

Code
``````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

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

In fact, I dont like this -

Code
``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

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• 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