I just want to bifurcate around 14900 rows of A column into 50 sets at each row in B column keeping @ as delimiter. Example: 12345@5432333@3232@... till 50th row of A column. Like that I need to build 50 sets till row count of A column(14900) in each row of B column.
I developed code for the same using =TEXTJOIN("@",TRUE,A1:A14900) but this is failing, may be due to length issues in single cell. my plan is to break the output of this formula into 50 sets and place the same into each row of B column. if i use loop to make the formula dynamic then im worrying the length of cell again
-------- --------
12345566 12345566@333344444@[email protected] 50th row
333344444 next 50 sets
98ZBRE322 next 50 sets
1ZAZAZAQQ next 50 sets
Is there any best way to solve this issue?
CodePudding user response:
Concatenate Ranges
Excel
In cell B1
use:
INDEX
=TEXTJOIN("@",TRUE,INDEX(A$1:A$14900,(ROW()-1)*298 1):INDEX(A$1:A$14900,ROW()*298))
OFFSET
(Volatile)
=TEXTJOIN("@",TRUE,OFFSET(A$1:A$298,(ROW()-1)*298,0))
and copy down to cell B50
.
VBA
Sub ConcatSets()
With ActiveSheet.Range("B1:B50")
' INDEX
.Formula = "=TEXTJOIN(""@"",TRUE,INDEX(A$1:A$14900,(ROW()-1)*298 1):INDEX(A$1:A$14900,ROW()*298))"
' OFFSET (Volatile)
'.Formula = "=TEXTJOIN(""@"",TRUE,OFFSET($A$1:$A$298,(ROW()-1)*298,0))"
' To keep only values use the following:
'.Value = .Value
End With
End Sub