Forum Discussion
How to separate numbers with brackets in a table
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?
If your version of Excel support dynamic arrays, that could be like this
Assume in H6 and I6 you have the size of the Range (number of columns and number of rows)
Positions could be extracted as
=INT( XMATCH(FILTER( INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)), LEFT(INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)),1)="["), INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)), 0)/$H$6)+1 & "," & MOD( XMATCH(FILTER( INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)), LEFT(INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)),1)="["), INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)), 0),$H$6)and numbers in brackets as
=NUMBERVALUE(SUBSTITUTE( SUBSTITUTE( FILTER( INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)), LEFT(INDEX($A$6:$F$15,TRANSPOSE(INT(SEQUENCE($I$6*$H$6,,$H$6)/$H$6)),TRANSPOSE(MOD(SEQUENCE($I$6*$H$6)-1,$H$6)+1)),1)="["), "[",""), "]","" ),",")Please check in attached.
I have a problem with the different excel languages - somewhy in Estonian the formula uses ";" instead of ",", so it is not working for me directly. (Also, the numbers are in Estonian with"," (for example 5,5) and in the US with ".".)
I can't change the language of my excel or add the formula translator plugin because I have no admin permission. I can only use an online formula translator but then I can't use your attached example file.
I'm sending an example of a full table. Can you check if it works with your generated formulas?Thank you so much for helping me so far.
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)="["), "[",""), "]","" ),",")