need to merge 3 columns in excel
up vote
2
down vote
favorite
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 at 11:14
Pᴇʜ
20.1k42650
asked Nov 20 at 11:12
suresh narasimman
225
add a comment |
up vote
2
down vote
favorite
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 at 11:14
Pᴇʜ
20.1k42650
asked Nov 20 at 11:12
suresh narasimman
225
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 at 11:15
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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 at 11:14
Pᴇʜ
20.1k42650
asked Nov 20 at 11:12
suresh narasimman
225
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 at 11:14
Pᴇʜ
20.1k42650
asked Nov 20 at 11:12
suresh narasimman
225
edited Nov 20 at 11:14
Pᴇʜ
20.1k42650
asked Nov 20 at 11:12
suresh narasimman
225
edited Nov 20 at 11:14
Pᴇʜ
20.1k42650
edited Nov 20 at 11:14
Pᴇʜ
20.1k42650
edited Nov 20 at 11:14
Pᴇʜ
20.1k42650
20.1k42650
asked Nov 20 at 11:12
suresh narasimman
225
asked Nov 20 at 11:12
suresh narasimman
225
asked Nov 20 at 11:12
suresh narasimman
225
225
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 at 11:15
add a comment |
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 at 11:15
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 at 11:15
try =CONCATENATE(D1,",",E1) etc
– W_O_L_F
Nov 20 at 11:15
add a comment |
4 Answers
4
active
oldest
votes
up vote
5
down vote
accepted
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 at 11:17
Xabier
6,4881418
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 at 13:47
add a comment |
up vote
3
down vote
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 at 11:17
Tim Biegeleisen
213k1385132
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 at 11:21
add a comment |
up vote
1
down vote
'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 at 11:24
Manoj Babu
312
add a comment |
up vote
1
down vote
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 at 11:17
Gary's Student
71.9k93561
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',
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
up vote
5
down vote
accepted
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 at 11:17
Xabier
6,4881418
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 at 13:47
add a comment |
up vote
5
down vote
accepted
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 at 11:17
Xabier
6,4881418
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 at 13:47
add a comment |
up vote
5
down vote
accepted
up vote
5
down vote
accepted
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 at 11:17
Xabier
6,4881418
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 at 11:17
Xabier
6,4881418
edited Nov 20 at 14:14
answered Nov 20 at 11:17
Xabier
6,4881418
answered Nov 20 at 11:17
Xabier
6,4881418
answered Nov 20 at 11:17
Xabier
6,4881418
6,4881418
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 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 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 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 at 13:47
add a comment |
up vote
3
down vote
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 at 11:17
Tim Biegeleisen
213k1385132
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 at 11:21
add a comment |
up vote
3
down vote
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 at 11:17
Tim Biegeleisen
213k1385132
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 at 11:21
add a comment |
up vote
3
down vote
up vote
3
down vote
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 at 11:17
Tim Biegeleisen
213k1385132
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 at 11:17
Tim Biegeleisen
213k1385132
edited Nov 20 at 11:23
answered Nov 20 at 11:17
Tim Biegeleisen
213k1385132
answered Nov 20 at 11:17
Tim Biegeleisen
213k1385132
answered Nov 20 at 11:17
Tim Biegeleisen
213k1385132
213k1385132
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 at 11:21
add a comment |
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 at 11:21
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 at 11:21
Note that CONCATENATE is deprecated and here only for compatibility, CONCAT being the replacement.
– Vincent G
Nov 20 at 11:21
add a comment |
up vote
1
down vote
'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 at 11:24
Manoj Babu
312
add a comment |
up vote
1
down vote
'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 at 11:24
Manoj Babu
312
add a comment |
up vote
1
down vote
up vote
1
down vote
'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 at 11:24
Manoj 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 at 11:24
Manoj Babu
312
answered Nov 20 at 11:24
Manoj Babu
312
answered Nov 20 at 11:24
Manoj Babu
312
answered Nov 20 at 11:24
Manoj Babu
312
312
add a comment |
add a comment |
up vote
1
down vote
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 at 11:17
Gary's Student
71.9k93561
add a comment |
up vote
1
down vote
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 at 11:17
Gary's Student
71.9k93561
add a comment |
up vote
1
down vote
up vote
1
down vote
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 at 11:17
Gary's Student
71.9k93561
If there are no spaces within the cells, then in D1 enter:
=SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1)," ",",")
and copy downwards:
answered Nov 20 at 11:17
Gary's Student
71.9k93561
edited Nov 20 at 11:26
answered Nov 20 at 11:17
Gary's Student
71.9k93561
answered Nov 20 at 11:17
Gary's Student
71.9k93561
answered Nov 20 at 11:17
Gary's Student
71.9k93561
71.9k93561
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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 at 11:15