Excel, how to put a named range list into one cell [duplicate]

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

This question already has an answer here:

Convert a column into a comma separated list

13 answers

How do I put my named range of a list of values into one cell?

i've tried going into a cell and naming the range like so =namedrange

sample data in my named range:

+---+----------+

|   |    A     |

+---+----------+

| 1 | 'stack', |

| 2 | 'over',  |

| 3 | 'flow',  |

+---+----------+

=OFFSET(Nodes!$B$2,0,0,COUNTA(Nodes!$B:$B),1)

expected output, i would like a formula to do this:

enter image description here

edited Feb 5 at 15:19

asked Feb 5 at 15:06

excelguy

758

marked as duplicate by Scott Craner, fixer1234, Pimp Juice IT, music2myear, Kenneth L Feb 13 at 2:02

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Hi Scott, do you have a textjoin function?

– excelguy
Feb 5 at 15:24

Go on Stack overflow and look at the answers for the TEXTJOIN tag. It is in there multiple times.

– Scott Craner
Feb 5 at 15:37

add a comment |

This question already has an answer here:

Convert a column into a comma separated list

13 answers

How do I put my named range of a list of values into one cell?

i've tried going into a cell and naming the range like so =namedrange

sample data in my named range:

+---+----------+

|   |    A     |

+---+----------+

| 1 | 'stack', |

| 2 | 'over',  |

| 3 | 'flow',  |

+---+----------+

=OFFSET(Nodes!$B$2,0,0,COUNTA(Nodes!$B:$B),1)

expected output, i would like a formula to do this:

enter image description here

edited Feb 5 at 15:19

asked Feb 5 at 15:06

excelguy

758

marked as duplicate by Scott Craner, fixer1234, Pimp Juice IT, music2myear, Kenneth L Feb 13 at 2:02

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Hi Scott, do you have a textjoin function?

– excelguy
Feb 5 at 15:24

Go on Stack overflow and look at the answers for the TEXTJOIN tag. It is in there multiple times.

– Scott Craner
Feb 5 at 15:37

add a comment |

This question already has an answer here:

Convert a column into a comma separated list

13 answers

How do I put my named range of a list of values into one cell?

i've tried going into a cell and naming the range like so =namedrange

sample data in my named range:

+---+----------+

|   |    A     |

+---+----------+

| 1 | 'stack', |

| 2 | 'over',  |

| 3 | 'flow',  |

+---+----------+

=OFFSET(Nodes!$B$2,0,0,COUNTA(Nodes!$B:$B),1)

expected output, i would like a formula to do this:

enter image description here

edited Feb 5 at 15:19

asked Feb 5 at 15:06

excelguy

758

This question already has an answer here:

Convert a column into a comma separated list

13 answers

How do I put my named range of a list of values into one cell?

i've tried going into a cell and naming the range like so =namedrange

sample data in my named range:

+---+----------+

|   |    A     |

+---+----------+

| 1 | 'stack', |

| 2 | 'over',  |

| 3 | 'flow',  |

+---+----------+

=OFFSET(Nodes!$B$2,0,0,COUNTA(Nodes!$B:$B),1)

expected output, i would like a formula to do this:

enter image description here

This question already has an answer here:

Convert a column into a comma separated list

13 answers

microsoft-excel

edited Feb 5 at 15:19

asked Feb 5 at 15:06

excelguy

758

edited Feb 5 at 15:19

asked Feb 5 at 15:06

excelguy

758

edited Feb 5 at 15:19

asked Feb 5 at 15:06

excelguy

758

asked Feb 5 at 15:06

excelguy

758

asked Feb 5 at 15:06

excelguy

758

marked as duplicate by Scott Craner, fixer1234, Pimp Juice IT, music2myear, Kenneth L Feb 13 at 2:02

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by Scott Craner, fixer1234, Pimp Juice IT, music2myear, Kenneth L Feb 13 at 2:02

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Hi Scott, do you have a textjoin function?

– excelguy
Feb 5 at 15:24

Go on Stack overflow and look at the answers for the TEXTJOIN tag. It is in there multiple times.

– Scott Craner
Feb 5 at 15:37

add a comment |

Hi Scott, do you have a textjoin function?

– excelguy
Feb 5 at 15:24

Go on Stack overflow and look at the answers for the TEXTJOIN tag. It is in there multiple times.

– Scott Craner
Feb 5 at 15:37

Hi Scott, do you have a textjoin function?

– excelguy
Feb 5 at 15:24

Go on Stack overflow and look at the answers for the TEXTJOIN tag. It is in there multiple times.

– Scott Craner
Feb 5 at 15:37

add a comment |

1 Answer
1

active

oldest

votes

Lets do 2 cases. Say the Named Range is A1 through A3. Without Named Ranges we can use:

=TEXTJOIN(CHAR(10),TRUE,A1:A3)

enter image description here

With Named Ranges:

=TEXTJOIN(CHAR(10),TRUE,Stooges)

gives the same result.

To use the comma as a separator, replace the 10 with 44

answered Feb 5 at 15:13

Gary's Student

14.2k31834

what is textjoin i dont seem to have that formula. I guess i dont have that excel version.

– excelguy
Feb 5 at 15:16

may be worth noting TEXTJOIN is not available in 2013 or earlier. not sure when it was brought in. I think @ScottCraner has VBA code to produce TEXTJOIN results.

– Forward Ed
Feb 5 at 15:17

add a comment |

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Lets do 2 cases. Say the Named Range is A1 through A3. Without Named Ranges we can use:

=TEXTJOIN(CHAR(10),TRUE,A1:A3)

enter image description here

With Named Ranges:

=TEXTJOIN(CHAR(10),TRUE,Stooges)

gives the same result.

To use the comma as a separator, replace the 10 with 44

answered Feb 5 at 15:13

Gary's Student

14.2k31834

what is textjoin i dont seem to have that formula. I guess i dont have that excel version.

– excelguy
Feb 5 at 15:16

may be worth noting TEXTJOIN is not available in 2013 or earlier. not sure when it was brought in. I think @ScottCraner has VBA code to produce TEXTJOIN results.

– Forward Ed
Feb 5 at 15:17

add a comment |

Lets do 2 cases. Say the Named Range is A1 through A3. Without Named Ranges we can use:

=TEXTJOIN(CHAR(10),TRUE,A1:A3)

enter image description here

With Named Ranges:

=TEXTJOIN(CHAR(10),TRUE,Stooges)

gives the same result.

To use the comma as a separator, replace the 10 with 44

answered Feb 5 at 15:13

Gary's Student

14.2k31834

what is textjoin i dont seem to have that formula. I guess i dont have that excel version.

– excelguy
Feb 5 at 15:16

may be worth noting TEXTJOIN is not available in 2013 or earlier. not sure when it was brought in. I think @ScottCraner has VBA code to produce TEXTJOIN results.

– Forward Ed
Feb 5 at 15:17

add a comment |

Lets do 2 cases. Say the Named Range is A1 through A3. Without Named Ranges we can use:

=TEXTJOIN(CHAR(10),TRUE,A1:A3)

enter image description here

With Named Ranges:

=TEXTJOIN(CHAR(10),TRUE,Stooges)

gives the same result.

To use the comma as a separator, replace the 10 with 44

answered Feb 5 at 15:13

Gary's Student

14.2k31834

Lets do 2 cases. Say the Named Range is A1 through A3. Without Named Ranges we can use:

=TEXTJOIN(CHAR(10),TRUE,A1:A3)

enter image description here

With Named Ranges:

=TEXTJOIN(CHAR(10),TRUE,Stooges)

gives the same result.

To use the comma as a separator, replace the 10 with 44

answered Feb 5 at 15:13

Gary's Student

14.2k31834

answered Feb 5 at 15:13

Gary's Student

14.2k31834

answered Feb 5 at 15:13

Gary's Student

14.2k31834

answered Feb 5 at 15:13

Gary's Student

14.2k31834

what is textjoin i dont seem to have that formula. I guess i dont have that excel version.

– excelguy
Feb 5 at 15:16

may be worth noting TEXTJOIN is not available in 2013 or earlier. not sure when it was brought in. I think @ScottCraner has VBA code to produce TEXTJOIN results.

– Forward Ed
Feb 5 at 15:17

add a comment |

what is textjoin i dont seem to have that formula. I guess i dont have that excel version.

– excelguy
Feb 5 at 15:16

may be worth noting TEXTJOIN is not available in 2013 or earlier. not sure when it was brought in. I think @ScottCraner has VBA code to produce TEXTJOIN results.

– Forward Ed
Feb 5 at 15:17

what is textjoin i dont seem to have that formula. I guess i dont have that excel version.

– excelguy
Feb 5 at 15:16

may be worth noting TEXTJOIN is not available in 2013 or earlier. not sure when it was brought in. I think @ScottCraner has VBA code to produce TEXTJOIN results.

– Forward Ed
Feb 5 at 15:17

add a comment |

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Csdrhrt