1

After applying CONCAT to the range of cells and trying to combine it with custom separators, function returns only the first object of the list.

Function that I'm using - =CONCAT(A1:A3&" "&ROW()-1&" ")

It seems that brackets on the sides doesn't let the whole list appear in the cell. I want to apply this function to the whole document, that's why pressing CONTROL+SHIFT+ENTER for each CONCAT doesn't seem to be right for me.

The result I am looking for would look like this:

ABC 
DBC
ASD
SSD
DFG
DFA
SDF
EGL
ASF

After CONCAT

ABC 0 DBC 1 ASD 2
SSD 3 DFG 4 DFA 5
SDF 6 EGL 7 ASF 8
Improve this question

2 Answers 2

Reset to default
0

As mentioned in comments on @MátéJuhász his answer you can use TEXTJOIN to do this (even CONCAT will still work with a small adjustment).

So with the sample data you gave you should try:

enter image description here

In B1 formula:

=TEXTJOIN(" ",TRUE,INDEX(A:A,1+(ROW()-1)*3):INDEX(A:A,3+(ROW()-1)*3)&" "&ROW(INDEX(A:A,1+(ROW()-1)*3):INDEX(A:A,3+(ROW()-1)*3))-1)

Note: It's an array formula and should be entered through CTRL+Shift+Enter

If you want to do this every nth row, simply change all the 3's in the formula to any number, e.g. 16.

And drag the formula down...:)

Improve this answer
1
  • NIIIIICE! Thank you for the explanation
    – Zhangir Siranov
    Aug 6, 2019 at 15:57
2

CONCATENATE works only with parameters provided as a list, and it can't handle multiple cells in a range.

Since Excel 2016 you can use CONCAT function for that (even without TRANSPOSE):

enter image description here

Unfortunately there is no solution with standard functions for that before Excel 2016.

Improve this answer
16
  • 1
    Maybe to keep the , delimiters you can include the TEXJOIN function :)
    – JvdV
    Aug 6, 2019 at 11:16
  • @JvdV how to use custom delimiters as a range of numbers on that? From example that would be ABC 0 DBL 1 SSD 2
    – Zhangir Siranov
    Aug 6, 2019 at 11:23
  • 1
    If you want it exactly like that, then CONCAT will do: =CONCAT(A1:A3&" "&ROW(A1:A3)-1) entered as array.
    – JvdV
    Aug 6, 2019 at 11:32
  • @ZhangirSiranov, would you want to use TEXTJOIN it would be =TEXTJOIN(" ",TRUE,A1:A3&" "&ROW(A1:A3)-1) as array formula.
    – JvdV
    Aug 6, 2019 at 11:42
  • 1
    @ZhangirSiranov, both options need to be confirmed through a combination of the keys CTRL+SHIFT+ENTER. Once they are entered as such, you can confirm that by checking the formula has curly brackets at the start and end.
    – JvdV
    Aug 6, 2019 at 12:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .