Formula or code for combines numbers with comma

V

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
543
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

We have the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
1​
[/td][td]
25​
[/td][td]
75​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
2​
[/td][td]
36​
[/td][td]
74​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
3​
[/td][td]
65​
[/td][td]
71​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
4​
[/td][td]
45​
[/td][td]
72​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
5​
[/td][td]
82​
[/td][td]
7​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
6​
[/td][td]
36​
[/td][td]
37​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
7​
[/td][td]
59​
[/td][td]
64​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
8​
[/td][td]
54​
[/td][td]
98​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
9​
[/td][td]
52​
[/td][td]
7​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
10​
[/td][td]
14​
[/td][td]
8​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
11​
[/td][td]
58​
[/td][td]
1​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
12​
[/td][td]
47​
[/td][td]
2​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

We want to combine the data B2:C13 in D2 and its paste special value in E2

something like this 25,36,65,45,82

data will be increase from B2:C160
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
In excel 2019 or office 365, either

=TEXTJOIN(",",1,B2:C13)

or, array confirmed with Ctrl Shift Enter

=TEXTJOIN(",",1,TRANSPOSE(B2:C13))

In older versions
Code: 
Public Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
    For Each y In a.Cells
        aconcat = aconcat & y.Value & sep
    Next y
ElseIf IsArray(a) Then
    For Each y In a
        aconcat = aconcat & y & sep
    Next y
Else
    aconcat = aconcat & a & sep
End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

=ACONCAT(B2:C13,",")

Note that only the second of the 3 methods suggested will produce the exact same format as you specified.
 
Upvote 0
If sequence is important, then consider the following

=TEXTJOIN(",",,B2:C13)
25,75,36,74,65,71,45,72,82,7,36,37,59,64,54,98,52,7,14,8,58,1,47,2
B2, C2, B3, C3......B13, C13

=TEXTJOIN(",",,B2:B13)&"," &TEXTJOIN(",",,C2:C13)
25,36,65,45,82,36,59,54,52,14,58,47,75,74,71,72,7,37,64,98,7,8,1,2
B2,B3.....B13, C2, C3 .....C13


ah - @Peter_SSs got in first :laugh:
 
Last edited:
Upvote 0
please check the error, no value are coming

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
1​
[/td][td]
25​
[/td][td]
75​
[/td][td]
=TEXTJOIN(",",,B2:C13)​
[/td][td]Yongle[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
2​
[/td][td]
36​
[/td][td]
74​
[/td][td]
=TEXTJOIN(",",1,B2:B13)&","&TEXTJOIN(",",1,C2:C13)​
[/td][td]Peter_SSs [/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
3​
[/td][td]
65​
[/td][td]
71​
[/td][td]
=TEXTJOIN(",",1,B2:C13)​
[/td][td]jasonb75[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
4​
[/td][td]
45​
[/td][td]
72​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
5​
[/td][td]
82​
[/td][td]
7​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
6​
[/td][td]
36​
[/td][td]
37​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
7​
[/td][td]
59​
[/td][td]
64​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
8​
[/td][td]
54​
[/td][td]
98​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
9​
[/td][td]
52​
[/td][td]
7​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
10​
[/td][td]
14​
[/td][td]
8​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
11​
[/td][td]
58​
[/td][td]
1​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
12​
[/td][td]
47​
[/td][td]
2​
[/td][td]
​
[/td][td]
​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

IInd

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
1​
[/td][td]
25​
[/td][td]
75​
[/td][td]
#NAME?​
[/td][td]Yongle[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
2​
[/td][td]
36​
[/td][td]
74​
[/td][td]
#NAME?​
[/td][td]Peter_SSs [/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
3​
[/td][td]
65​
[/td][td]
71​
[/td][td]
#NAME?​
[/td][td]jasonb75[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
4​
[/td][td]
45​
[/td][td]
72​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
5​
[/td][td]
82​
[/td][td]
7​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
6​
[/td][td]
36​
[/td][td]
37​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
7​
[/td][td]
59​
[/td][td]
64​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
8​
[/td][td]
54​
[/td][td]
98​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
9​
[/td][td]
52​
[/td][td]
7​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
10​
[/td][td]
14​
[/td][td]
8​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
11​
[/td][td]
58​
[/td][td]
1​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
12​
[/td][td]
47​
[/td][td]
2​
[/td][td]
​
[/td][td]
​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Last edited:
Upvote 0
Hi,

TextJoin appeared with Excel 2016.
Looks like you're using an older version of excel.
if you have Excel 2016 or higher then maybe because of the regional settings.
Change, in the formula "," with ";" obviously without the quotation marks.
 
Upvote 0
Vishaal said:
please check the error, no value are coming
Click to expand...
You were given the following but have not commented on that ..

jasonb75 said:
In excel 2019 or office 365, either

=TEXTJOIN(",",1,B2:C13)

or, array confirmed with Ctrl Shift Enter

=TEXTJOIN(",",1,TRANSPOSE(B2:C13))

In older versions
Code: 
Public Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
    For Each y In a.Cells
        aconcat = aconcat & y.Value & sep
    Next y
ElseIf IsArray(a) Then
    For Each y In a
        aconcat = aconcat & y & sep
    Next y
Else
    aconcat = aconcat & a & sep
End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

=ACONCAT(B2:C13,",")
Click to expand...
 
Upvote 0
@jasonb75 qualified his solution with "in excel 2019 or office 365 "
explanation:
#NAME? error tells you that the function is not available in your version of Excel (= Excel cannot find function with that NAME)
 
Last edited:
Upvote 0
Tom.Jones said:
.. regional settings.
Change, in the formula "," with ";"
Click to expand...
That issue wouldn't normally result in the #NAME ? error though.


Another option is to go back to basics. :)

=B2&","&B3&","&B4&","&B5&","&B6&","&B7&","&B8&","&B9&","&B10&","&B11&","&B12&","&B13&","&C2&","&C3&","&C4&","&C5&","&C6&","&C7&","&C8&","&C9&","&C10&","&C11&","&C12&","&C13
 
Upvote 0
Excel 2010

Another option is to go back to basics. :smile: only this option...:rofl:
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
copy from source sheet1 to destination sheet2 vba code
Replies
1
Views
250
Vishaal
V
P
  • Question Question
Help with Vba Code
Replies
3
Views
463
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
D
  • Solved
Code to move rows to a new sheet.
Replies
4
Views
705
dandee14k
D
D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
786
portews
P

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

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