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)="["), "[",""), "]","" ),",")
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)="["),
"[",""),
"]",""
),",")
- Lennart5Apr 13, 2020Copper Contributor
SergeiBaklan If I change 1 number in the table the formula will not work and will give me "#NAME?". I think it is because of the language problem.
I guess for the next tables I have to count it manually or do different method 😄
Big thanks again for your great response.- SergeiBaklanApr 16, 2020Diamond Contributor
Most probably your version of Excel doesn't support dynamic arrays.