Forum Discussion
How to separate numbers with brackets in a table
I have one table where I have normal numbers and numbers with brackets. I need a new table where I have only numbers with brackets.
For example this table
| 1 | 2 | 4 | [5] | 6 | |
| 5 | [38,3] | 6 | |||
| 223,3 | 0 | 23 | [323] | [43] | 1,2 |
...has to go automatically to this:
| 1,1 | 2,1 | 3,1 | 3,2 | (row number, number in row) | |
| 5 | 38,3 | 323 | 43 | ||
Does anyone know the formulas for this? (The file is actually with 40000 cells)
Thank you
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)="["), "[",""), "]","" ),",")
8 Replies
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?
- Like this or just 1 row containing numbers with brackets without brackets.
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.
SergeiBaklan Yes.
1. row "row number - comma - number ordinal number in the row"
2. row the number with brackets.
