Forum Discussion
How to separate numbers with brackets in a table
- Apr 13, 2020
It shall be no problem with that - if you open the file I attached for the previous post, Excel automatically will show you all formulas and numbers format for the Estonian locale. There is no need to translate formulas published in the text of the post.
However,
1) If you have no file and would like to translate some formulas I'd recommend this online tool https://excel-translator.de/
2) In attached file formulas are adjusted for your actual range. Please note, opening it I see everything for English SKU, you will see the same for Estonian one.
First, to simplify the formulas I added named ranges
Formulas itself are located here
and they are
=INT( XMATCH(FILTER( INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1) ), LEFT( INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1) ),1 )="["), INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1) ), 0)/nColumns)+1 & "," & MOD( XMATCH(FILTER( INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1) ), LEFT(INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1)),1)="["), INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1)), 0),nColumns)
and
=NUMBERVALUE(SUBSTITUTE( SUBSTITUTE( FILTER( INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1) ), LEFT( INDEX(Range, TRANSPOSE(INT(SEQUENCE(nRows*nColumns,,nColumns)/nColumns)), TRANSPOSE(MOD(SEQUENCE(nRows*nColumns)-1,nColumns)+1) ), 1)="["), "[",""), "]","" ),",")
Do I understand correctly in resulting table there are only two rows - in first one positions of numbers in brackets in source data, and in second one numbers themselves?
SergeiBaklan Yes.
1. row "row number - comma - number ordinal number in the row"
2. row the number with brackets.