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

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
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
ABCDEFGHIJKL
1
2
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
16
17
18
Sheet4
Cell Formulas
RangeFormula
K6:K15K6=SUM(F6:J6)


Thank you.

Regards,
Moti
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Here is a VBA way.

motil
DEFGHIJK
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
Sheet1
Cell Formulas
RangeFormula
F6:J15F6=SPLITIT($D6,COLUMNS($A$1:A1))
K6:K15K6=SUM(F6:J6)


VBA Code:
Function SPLITIT(s As String, n As Integer) As Integer
SPLITIT = Split(s, " ")(n - 1)
End Function
 
Upvote 1
Here is a VBA way.

motil
DEFGHIJK
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
Sheet1
Cell Formulas
RangeFormula
F6:J15F6=SPLITIT($D6,COLUMNS($A$1:A1))
K6:K15K6=SUM(F6:J6)


VBA Code:
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 :)
 
Upvote 0
T202306a.xlsm
ABCDEFGHIJK
1Try Data TextToColumns
2
3Numbers
4Seperated
5By Space
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
16
1a
Cell Formulas
RangeFormula
K6:K15K6=SUM(F6:J6)
 
Upvote 0
Formula option for older Excel version (2016 or before)

What I am trying to achieve (2).xlsx
ABCDEFGHIJK
1
2
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
Sheet2
Cell Formulas
RangeFormula
F6:J15F6=TRIM(MID(SUBSTITUTE($D6," ",REPT(" ",100)),(COLUMNS($A:A)-1)*100+1,100))+0
K6:K15K6=SUM(F6:J6)
 
Upvote 1
Formula option for older Excel version (2016 or before)

What I am trying to achieve (2).xlsx
ABCDEFGHIJK
1
2
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
Sheet2
Cell Formulas
RangeFormula
F6:J15F6=TRIM(MID(SUBSTITUTE($D6," ",REPT(" ",100)),(COLUMNS($A:A)-1)*100+1,100))+0
K6:K15K6=SUM(F6:J6)
I was waiting for a formula solution. Very slick.
 
Upvote 0
Formula option for older Excel version (2016 or before)

What I am trying to achieve (2).xlsx
ABCDEFGHIJK
1
2
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
Sheet2
Cell Formulas
RangeFormula
F6:J15F6=TRIM(MID(SUBSTITUTE($D6," ",REPT(" ",100)),(COLUMNS($A:A)-1)*100+1,100))+0
K6:K15K6=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 :)
 
Upvote 0
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.

VBA Code:
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
DEFGHIJK
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
Sheet3
Cell Formulas
RangeFormula
F6:J15F6=MID(SUBSTITUTE(" "&$D6," ",REPT(" ",100)),COLUMNS($F:F)*100,100)+0
K6:K15K6=SUM(F6:J6)
 
Upvote 1
Solution
VBA alternative to split and sum.

VBA Code:
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
DEFGHIJK
3Numbers
4Seperated
5By Spacen1n2n3n4n5Sum
61 2 4 5 71245719
71 2 3 4 1012341020
81 2 3 32 50123325088
91 2 3 33 49123334988
101 2 24 29 311224293187
111 2 25 27 321225273287
129 32 34 35 36932343536146
139 32 33 34 39932333439147
1410 16 17 21 22101617212286
1510 16 25 40 431016254043134
Sheet3
Cell Formulas
RangeFormula
F6:J15F6=MID(SUBSTITUTE(" "&$D6," ",REPT(" ",100)),COLUMNS($F:F)*100,100)+0
K6:K15K6=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 :)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top