2

I have n number of invoices in my excel sheet, say for example, column A of Sheet1 has 1 lakh records. Now I want to concatenate them with ; as a separator. Say, x = 300 (x can vary based on requirement), I want first 300 records, i.e., A1-A300, as 1 set, next 300 records makes another set and so on. So, if Column A has invoices and Column B has ; than in Column C, I can do it using & operator, something like this:

=A2&B2
=C2&A3&B3
=C3&A4&B4

But the problem with this method is, I have to break manually after every x records.

I came up with another formula using TEXTJOIN function, which is: =TEXTJOIN(";",FALSE,A1:A300). This works very nice, but the problem is if I drag it to next line the formula starts from A2. So in cell C2 the formula will be =TEXTJOIN(";",FALSE,A2:A301), in C3 it will be =TEXTJOIN(";",FALSE,A3:A302) and so on... But I expect in cell C2 like this: =TEXTJOIN(";",FALSE,A301:A600).

note: x is not fixed. x can be any number based on user requirement. Also I don't want to record a macro.

Can anyone please help me with this? Do let me know if my question is not clear and you want any clarity about it. Thanks in advance.

3

You can use TEXTJOIN and INDIRECT to do this.

Assuming your X value was in E4, you could use this formula in B1 and drag down.

=TEXTJOIN(";",FALSE,INDIRECT("A"&ROW()*$E$4-$E$4+1&":A"&ROW()*$E$4))
1
  • Hi PeterH. Yes this works superb! Works even if there are blank records. Thanks buddy
    – aCoder
    Oct 5 '20 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.