need to merge 3 columns in excel
I have 3 columns A,B,C and I need to merge the 3 columns and I have applied the forumala =A1&","&B1&","&C1 the output came as E column I need the output as D column.
excel vba excel-vba excel-formula
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
add a comment |
I have 3 columns A,B,C and I need to merge the 3 columns and I have applied the forumala =A1&","&B1&","&C1 the output came as E column I need the output as D column.
excel vba excel-vba excel-formula
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 '18 at 11:15
add a comment |
I have 3 columns A,B,C and I need to merge the 3 columns and I have applied the forumala =A1&","&B1&","&C1 the output came as E column I need the output as D column.
excel vba excel-vba excel-formula
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
I have 3 columns A,B,C and I need to merge the 3 columns and I have applied the forumala =A1&","&B1&","&C1 the output came as E column I need the output as D column.
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
edited Nov 20 '18 at 11:14
Pᴇʜ
22.8k62850
22.8k62850
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
asked Nov 20 '18 at 11:12
suresh narasimmansuresh narasimman
285
285
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 '18 at 11:15
add a comment |
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 '18 at 11:15
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 '18 at 11:15
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 '18 at 11:15
add a comment |
4 Answers
4
active
oldest
votes
The following formula will achieve your desired result:
=TEXTJOIN(",",TRUE,A1:C1)
Textjoin works like concatenate but can have a delimiter as an argument, also it gives you the ability to ignore blank cells, the first argument is the delimiter, the second is the flag to ignore blanks and the third is for the range.
As comments do mention that TEXTJOIN is only available for Office 365 subscribers, a possible alternative would be to build your UDF as below, this will allow you to use the formula above without an Office 365 subscription:
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
Dim compiled As String
For Each cell In rng
If ignore_empty And IsEmpty(cell.Value) Then
'nothing
Else
compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.Value)
End If
Next
TEXTJOIN = compiled
End Function
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
1
Worth to mention that "TEXTJOIN is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office."
– Pᴇʜ
Nov 20 '18 at 13:47
add a comment |
Enter this formula into E1:
=CONCATENATE(A1, IF(AND(B1<>"", A1<>""), ",", ""), B1,
IF(AND(OR(A1<>"", B1<>""), C1<>""), ",", ""), C1)
Using TEXTJOIN might be a cleaner option, but is only available on more recent versions of Excel.
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 '18 at 11:21
add a comment |
'Put this code in module and use formula concmulti and select the range
Function concmulti(slt As Range) As String
Dim str As String
Dim cell As Range
For Each cell In slt
str = str & cell.Value & ", "
Next cell
concmulti = str
End Function
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
add a comment |
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391744%2fneed-to-merge-3-columns-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
The following formula will achieve your desired result:
=TEXTJOIN(",",TRUE,A1:C1)
Textjoin works like concatenate but can have a delimiter as an argument, also it gives you the ability to ignore blank cells, the first argument is the delimiter, the second is the flag to ignore blanks and the third is for the range.
As comments do mention that TEXTJOIN is only available for Office 365 subscribers, a possible alternative would be to build your UDF as below, this will allow you to use the formula above without an Office 365 subscription:
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
Dim compiled As String
For Each cell In rng
If ignore_empty And IsEmpty(cell.Value) Then
'nothing
Else
compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.Value)
End If
Next
TEXTJOIN = compiled
End Function
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
1
Worth to mention that "TEXTJOIN is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office."
– Pᴇʜ
Nov 20 '18 at 13:47
add a comment |
The following formula will achieve your desired result:
=TEXTJOIN(",",TRUE,A1:C1)
Textjoin works like concatenate but can have a delimiter as an argument, also it gives you the ability to ignore blank cells, the first argument is the delimiter, the second is the flag to ignore blanks and the third is for the range.
As comments do mention that TEXTJOIN is only available for Office 365 subscribers, a possible alternative would be to build your UDF as below, this will allow you to use the formula above without an Office 365 subscription:
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
Dim compiled As String
For Each cell In rng
If ignore_empty And IsEmpty(cell.Value) Then
'nothing
Else
compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.Value)
End If
Next
TEXTJOIN = compiled
End Function
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
1
Worth to mention that "TEXTJOIN is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office."
– Pᴇʜ
Nov 20 '18 at 13:47
add a comment |
The following formula will achieve your desired result:
=TEXTJOIN(",",TRUE,A1:C1)
Textjoin works like concatenate but can have a delimiter as an argument, also it gives you the ability to ignore blank cells, the first argument is the delimiter, the second is the flag to ignore blanks and the third is for the range.
As comments do mention that TEXTJOIN is only available for Office 365 subscribers, a possible alternative would be to build your UDF as below, this will allow you to use the formula above without an Office 365 subscription:
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
Dim compiled As String
For Each cell In rng
If ignore_empty And IsEmpty(cell.Value) Then
'nothing
Else
compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.Value)
End If
Next
TEXTJOIN = compiled
End Function
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
The following formula will achieve your desired result:
=TEXTJOIN(",",TRUE,A1:C1)
Textjoin works like concatenate but can have a delimiter as an argument, also it gives you the ability to ignore blank cells, the first argument is the delimiter, the second is the flag to ignore blanks and the third is for the range.
As comments do mention that TEXTJOIN is only available for Office 365 subscribers, a possible alternative would be to build your UDF as below, this will allow you to use the formula above without an Office 365 subscription:
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
Dim compiled As String
For Each cell In rng
If ignore_empty And IsEmpty(cell.Value) Then
'nothing
Else
compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.Value)
End If
Next
TEXTJOIN = compiled
End Function
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
edited Nov 20 '18 at 14:14
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
answered Nov 20 '18 at 11:17
XabierXabier
6,6181418
6,6181418
1
Worth to mention that "TEXTJOIN is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office."
– Pᴇʜ
Nov 20 '18 at 13:47
add a comment |
1
Worth to mention that "TEXTJOIN is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office."
– Pᴇʜ
Nov 20 '18 at 13:47
1
1
Worth to mention that "TEXTJOIN is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office."
– Pᴇʜ
Nov 20 '18 at 13:47
Worth to mention that "TEXTJOIN is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office."
– Pᴇʜ
Nov 20 '18 at 13:47
add a comment |
Enter this formula into E1:
=CONCATENATE(A1, IF(AND(B1<>"", A1<>""), ",", ""), B1,
IF(AND(OR(A1<>"", B1<>""), C1<>""), ",", ""), C1)
Using TEXTJOIN might be a cleaner option, but is only available on more recent versions of Excel.
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 '18 at 11:21
add a comment |
Enter this formula into E1:
=CONCATENATE(A1, IF(AND(B1<>"", A1<>""), ",", ""), B1,
IF(AND(OR(A1<>"", B1<>""), C1<>""), ",", ""), C1)
Using TEXTJOIN might be a cleaner option, but is only available on more recent versions of Excel.
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 '18 at 11:21
add a comment |
Enter this formula into E1:
=CONCATENATE(A1, IF(AND(B1<>"", A1<>""), ",", ""), B1,
IF(AND(OR(A1<>"", B1<>""), C1<>""), ",", ""), C1)
Using TEXTJOIN might be a cleaner option, but is only available on more recent versions of Excel.
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
Enter this formula into E1:
=CONCATENATE(A1, IF(AND(B1<>"", A1<>""), ",", ""), B1,
IF(AND(OR(A1<>"", B1<>""), C1<>""), ",", ""), C1)
Using TEXTJOIN might be a cleaner option, but is only available on more recent versions of Excel.
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
edited Nov 20 '18 at 11:23
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
answered Nov 20 '18 at 11:17
Tim BiegeleisenTim Biegeleisen
226k1392145
226k1392145
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 '18 at 11:21
add a comment |
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 '18 at 11:21
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 '18 at 11:21
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 '18 at 11:21
add a comment |
'Put this code in module and use formula concmulti and select the range
Function concmulti(slt As Range) As String
Dim str As String
Dim cell As Range
For Each cell In slt
str = str & cell.Value & ", "
Next cell
concmulti = str
End Function
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
add a comment |
'Put this code in module and use formula concmulti and select the range
Function concmulti(slt As Range) As String
Dim str As String
Dim cell As Range
For Each cell In slt
str = str & cell.Value & ", "
Next cell
concmulti = str
End Function
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
add a comment |
'Put this code in module and use formula concmulti and select the range
Function concmulti(slt As Range) As String
Dim str As String
Dim cell As Range
For Each cell In slt
str = str & cell.Value & ", "
Next cell
concmulti = str
End Function
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
'Put this code in module and use formula concmulti and select the range
Function concmulti(slt As Range) As String
Dim str As String
Dim cell As Range
For Each cell In slt
str = str & cell.Value & ", "
Next cell
concmulti = str
End Function
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
answered Nov 20 '18 at 11:24
Manoj BabuManoj Babu
312
312
add a comment |
add a comment |
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
add a comment |
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
add a comment |
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
edited Nov 20 '18 at 11:26
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
answered Nov 20 '18 at 11:17
Gary's StudentGary's Student
73.1k94062
73.1k94062
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391744%2fneed-to-merge-3-columns-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 '18 at 11:15