Separate one cell “5 numbers” separated by one “space”…. in to separate columns.

[motilulla]

Using Excel 2010

Hello,

I get 5 numbers into 1 cell, in the column “D” separated by one space. I want to put them into separated cells in the columns F through J and sum in the column K

Please help with formula or VBA….below are the example data.

Excel Question ExcelFourms.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3				Numbers
4				Seperated
5				By Space		n1	n2	n3	n4	n5	Sum
6				1 2 4 5 7		1	2	4	5	7	19
7				1 2 3 4 10		1	2	3	4	10	20
8				1 2 3 32 50		1	2	3	32	50	88
9				1 2 3 33 49		1	2	3	33	49	88
10				1 2 24 29 31		1	2	24	29	31	87
11				1 2 25 27 32		1	2	25	27	32	87
12				9 32 34 35 36		9	32	34	35	36	146
13				9 32 33 34 39		9	32	33	34	39	147
14				10 16 17 21 22		10	16	17	21	22	86
15				10 16 25 40 43		10	16	25	40	43	134
16
17
18
Sheet4

Cell Formulas
Range	Formula
K6:K15	K6	=SUM(F6:J6)

Thank you.

Regards,
Moti

 

[lrobbo314]

Here is a VBA way.

motil
	D	E	F	G	H	I	J	K
3	Numbers
4	Seperated
5	By Space		n1	n2	n3	n4	n5	Sum
6	1 2 4 5 7		1	2	4	5	7	19
7	1 2 3 4 10		1	2	3	4	10	20
8	1 2 3 32 50		1	2	3	32	50	88
9	1 2 3 33 49		1	2	3	33	49	88
10	1 2 24 29 31		1	2	24	29	31	87
11	1 2 25 27 32		1	2	25	27	32	87
12	9 32 34 35 36		9	32	34	35	36	146
13	9 32 33 34 39		9	32	33	34	39	147
14	10 16 17 21 22		10	16	17	21	22	86
15	10 16 25 40 43		10	16	25	40	43	134
Sheet1

Cell Formulas
Range	Formula
F6:J15	F6	=SPLITIT($D6,COLUMNS($A$1:A1))
K6:K15	K6	=SUM(F6:J6)

Function SPLITIT(s As String, n As Integer) As Integer
SPLITIT = Split(s, " ")(n - 1)
End Function

 

[motilulla]

Here is a VBA way.

motil
	D	E	F	G	H	I	J	K
3	Numbers
4	Seperated
5	By Space		n1	n2	n3	n4	n5	Sum
6	1 2 4 5 7		1	2	4	5	7	19
7	1 2 3 4 10		1	2	3	4	10	20
8	1 2 3 32 50		1	2	3	32	50	88
9	1 2 3 33 49		1	2	3	33	49	88
10	1 2 24 29 31		1	2	24	29	31	87
11	1 2 25 27 32		1	2	25	27	32	87
12	9 32 34 35 36		9	32	34	35	36	146
13	9 32 33 34 39		9	32	33	34	39	147
14	10 16 17 21 22		10	16	17	21	22	86
15	10 16 25 40 43		10	16	25	40	43	134
Sheet1

Cell Formulas
Range	Formula
F6:J15	F6	=SPLITIT($D6,COLUMNS($A$1:A1))
K6:K15	K6	=SUM(F6:J6)

Function SPLITIT(s As String, n As Integer) As Integer
SPLITIT = Split(s, " ")(n - 1)
End Function

Hello @lrobbo314, it worked perfect! Thank you so much for your help and you took time to answer of my query.

Have a nice day and good luck!

Kind Regards,
Moti

 

[Dave Patton]

T202306a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1				Try Data TextToColumns
2
3				Numbers
4				Seperated
5				By Space
6				1 2 4 5 7		1	2	4	5	7	19
7				1 2 3 4 10		1	2	3	4	10	20
8				1 2 3 32 50		1	2	3	32	50	88
9				1 2 3 33 49		1	2	3	33	49	88
10				1 2 24 29 31		1	2	24	29	31	87
11				1 2 25 27 32		1	2	25	27	32	87
12				9 32 34 35 36		9	32	34	35	36	146
13				9 32 33 34 39		9	32	33	34	39	147
14				10 16 17 21 22		10	16	17	21	22	86
15				10 16 25 40 43		10	16	25	40	43	134
16
1a

Cell Formulas
Range	Formula
K6:K15	K6	=SUM(F6:J6)

 

[bebo021999]

Formula option for older Excel version (2016 or before)

What I am trying to achieve (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3				Numbers
4				Seperated
5				By Space		n1	n2	n3	n4	n5	Sum
6				1 2 4 5 7		1	2	4	5	7	19
7				1 2 3 4 10		1	2	3	4	10	20
8				1 2 3 32 50		1	2	3	32	50	88
9				1 2 3 33 49		1	2	3	33	49	88
10				1 2 24 29 31		1	2	24	29	31	87
11				1 2 25 27 32		1	2	25	27	32	87
12				9 32 34 35 36		9	32	34	35	36	146
13				9 32 33 34 39		9	32	33	34	39	147
14				10 16 17 21 22		10	16	17	21	22	86
15				10 16 25 40 43		10	16	25	40	43	134
Sheet2

Cell Formulas
Range	Formula
F6:J15	F6	=TRIM(MID(SUBSTITUTE($D6," ",REPT(" ",100)),(COLUMNS($A:A)-1)*100+1,100))+0
K6:K15	K6	=SUM(F6:J6)

 

[lrobbo314]

Formula option for older Excel version (2016 or before)

What I am trying to achieve (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3				Numbers
4				Seperated
5				By Space		n1	n2	n3	n4	n5	Sum
6				1 2 4 5 7		1	2	4	5	7	19
7				1 2 3 4 10		1	2	3	4	10	20
8				1 2 3 32 50		1	2	3	32	50	88
9				1 2 3 33 49		1	2	3	33	49	88
10				1 2 24 29 31		1	2	24	29	31	87
11				1 2 25 27 32		1	2	25	27	32	87
12				9 32 34 35 36		9	32	34	35	36	146
13				9 32 33 34 39		9	32	33	34	39	147
14				10 16 17 21 22		10	16	17	21	22	86
15				10 16 25 40 43		10	16	25	40	43	134
Sheet2

Cell Formulas
Range	Formula
F6:J15	F6	=TRIM(MID(SUBSTITUTE($D6," ",REPT(" ",100)),(COLUMNS($A:A)-1)*100+1,100))+0
K6:K15	K6	=SUM(F6:J6)

I was waiting for a formula solution. Very slick.

 

[motilulla]

Formula option for older Excel version (2016 or before)

What I am trying to achieve (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3				Numbers
4				Seperated
5				By Space		n1	n2	n3	n4	n5	Sum
6				1 2 4 5 7		1	2	4	5	7	19
7				1 2 3 4 10		1	2	3	4	10	20
8				1 2 3 32 50		1	2	3	32	50	88
9				1 2 3 33 49		1	2	3	33	49	88
10				1 2 24 29 31		1	2	24	29	31	87
11				1 2 25 27 32		1	2	25	27	32	87
12				9 32 34 35 36		9	32	34	35	36	146
13				9 32 33 34 39		9	32	33	34	39	147
14				10 16 17 21 22		10	16	17	21	22	86
15				10 16 25 40 43		10	16	25	40	43	134
Sheet2

Cell Formulas
Range	Formula
F6:J15	F6	=TRIM(MID(SUBSTITUTE($D6," ",REPT(" ",100)),(COLUMNS($A:A)-1)*100+1,100))+0
K6:K15	K6	=SUM(F6:J6)

Hello @bebo021999, True your formula worked for me with 50000+ rows magically.

I was also waiting for a formula solution. Thanks for your help.

Have a nice day and good luck!

Kind Regards,
Moti

 

[Peter_SSs]

I want to put them into separated cells in the columns F through J and sum in the column K

Please help with formula or VBA

VBA alternative to split and sum.

Sub TTC_and_Sum()
  With Range("D6", Range("D" & Rows.Count).End(xlUp))
    .TextToColumns Destination:=Range("F6"), DataType:=xlDelimited, Space:=True, Other:=False
    .Offset(, 7).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
  End With
End Sub

Formula alternative
Similar to @bebo021999 except the TRIM function is superfluous so can be omitted.

motilulla.xlsm
	D	E	F	G	H	I	J	K
3	Numbers
4	Seperated
5	By Space		n1	n2	n3	n4	n5	Sum
6	1 2 4 5 7		1	2	4	5	7	19
7	1 2 3 4 10		1	2	3	4	10	20
8	1 2 3 32 50		1	2	3	32	50	88
9	1 2 3 33 49		1	2	3	33	49	88
10	1 2 24 29 31		1	2	24	29	31	87
11	1 2 25 27 32		1	2	25	27	32	87
12	9 32 34 35 36		9	32	34	35	36	146
13	9 32 33 34 39		9	32	33	34	39	147
14	10 16 17 21 22		10	16	17	21	22	86
15	10 16 25 40 43		10	16	25	40	43	134
Sheet3

Cell Formulas
Range	Formula
F6:J15	F6	=MID(SUBSTITUTE(" "&$D6," ",REPT(" ",100)),COLUMNS($F:F)*100,100)+0
K6:K15	K6	=SUM(F6:J6)

 

[motilulla]

VBA alternative to split and sum.

Sub TTC_and_Sum()
  With Range("D6", Range("D" & Rows.Count).End(xlUp))
    .TextToColumns Destination:=Range("F6"), DataType:=xlDelimited, Space:=True, Other:=False
    .Offset(, 7).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
  End With
End Sub

Formula alternative
Similar to @bebo021999 except the TRIM function is superfluous so can be omitted.

motilulla.xlsm
	D	E	F	G	H	I	J	K
3	Numbers
4	Seperated
5	By Space		n1	n2	n3	n4	n5	Sum
6	1 2 4 5 7		1	2	4	5	7	19
7	1 2 3 4 10		1	2	3	4	10	20
8	1 2 3 32 50		1	2	3	32	50	88
9	1 2 3 33 49		1	2	3	33	49	88
10	1 2 24 29 31		1	2	24	29	31	87
11	1 2 25 27 32		1	2	25	27	32	87
12	9 32 34 35 36		9	32	34	35	36	146
13	9 32 33 34 39		9	32	33	34	39	147
14	10 16 17 21 22		10	16	17	21	22	86
15	10 16 25 40 43		10	16	25	40	43	134
Sheet3

Cell Formulas
Range	Formula
F6:J15	F6	=MID(SUBSTITUTE(" "&$D6," ",REPT(" ",100)),COLUMNS($F:F)*100,100)+0
K6:K15	K6	=SUM(F6:J6)

Hello Peter_SSs, Stored your both options worked flawless.

Thank you for your support and time.

Have a nice day and good luck!

Kind Regards,
Moti

 

[Peter_SSs]

Cheers. Glad to help.