Forum Discussion
Concatenate elements via formula
Hello everybody!
Hope you're great. I have a problem: I am quite new in using Excel, and I am trying to solve it but without useful results.
I have a large list of numbers (say e.g. 50) in one column and once I fix a rate number, I want to generate different rows with the concatenation of a rate number of the elements in the first column.
For example, if in Column A I have number from 1 to 50 and my rate is 10, i want cell (say) B1 to have the following string: "1; 2; 3; 4; 5; 6; 7; 8; 9; 10; ". Then cell B2 to have: "11; 12; 13; 14; 15; 16; 17; 18; 19; 20; " and so on, until cell B5 to have: " 41; 42; 43; 44; 45; 46; 47; 48; 49; 50". I would like to make this process authomaticly using some combination of formulae, but still cannot manage to find the right one. I attach the file in which I am making my tries.
If you know how to solve this and help me, I'll be extremely grateful to you. 🙂
Hi LM_95
One way assuming your version of Excel (you did not mention it as asked in Welcome to your Excel discussion space!) has function TEXTJOIN
in B4 and copy down as necessary:
=TEXTJOIN(", ",TRUE,OFFSET(A$4,(ROW()-ROW(A$4))*B$1,,B$1))Your file with the above formula is attached
5 Replies
Hi LM_95
One way assuming your version of Excel (you did not mention it as asked in Welcome to your Excel discussion space!) has function TEXTJOIN
in B4 and copy down as necessary:
=TEXTJOIN(", ",TRUE,OFFSET(A$4,(ROW()-ROW(A$4))*B$1,,B$1))Your file with the above formula is attached
If you are open to a VBA solution, please find the attached which contains Sheet Change Code on Foglio 2 Sheet Module and a macro on Module1.
On Foglio 2 Sheet, if you change the rate in cell T2 (yellow cell), you will get the desired output in column B.
- Dear Subodh_Tiwari_sktneer,
Thank you very much for your answer, I really appreciate it.
Unfortunately, I am not used to VBA and macros, so is there a way I can do it via "basic" formulas without VBAs nor macros?
