Find numbers in Brackets

[pagrender]

Hello all,

I'm trying to write formulas in cells AV2:AY6 based on the "bracketed" values in column A. For instance, in A3, you can see a [1] and a [99]. I would like the values in AV3 be 1% and AW3 be 99%.

If there are no brackets (and the length of column A is 23 - it will always be a length of 23 when no brackets), the percent in column AV should indicate 100%, followed by 0% in the other three cells in that row.

Data will always be in the formats you see below.

Bascially, I will always need to return the value from between the brackets. If there are no brackets, return a 100% in the first cell with zeros in the remaining cells.

Column AZ should always add up to 100%

Can someone help me? Let me know if you need any additional explanations.

Thanks!

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 556px"><COL style="WIDTH: 33px"><COL style="WIDTH: 27px"><COL style="WIDTH: 34px"><COL style="WIDTH: 28px"><COL style="WIDTH: 33px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>AV</TD><TD>AW</TD><TD>AX</TD><TD>AY</TD><TD>AZ</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-SIZE: 8pt; FONT-WEIGHT: bold">Account Number</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt; FONT-WEIGHT: bold">One</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Two</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Three</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Four</TD><TD style="FONT-SIZE: 8pt">Total</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 8pt">10 E 100 173 120100 000</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 8pt">10 E 807 183 212000 335[1] 10 E 807 183 212730 197[99]</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">1%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">99%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 8pt">10 E 807 183 212000 335[76] 10 E 807 183 212730 197[24]</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">76%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">24%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt">10 E 807 183 212114 332[50] 27 E 800 183 212000 011[30] 10 E 800 183 212000 000[20]</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">50%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">30%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">20%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">0%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt">10 E 807 183 212114 332[50] 27 E 800 183 212000 011[1] 10 E 800 183 212000 000[49]</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">35%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">25%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">20%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">20%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 8pt">27 E 800 183 212000 011[35] 10 E 800 183 212000 000[25] 10 E 807 183 212730 397[20] 10 E 807 183 212403 367[20]</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">35%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">25%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">20%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">20%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 8pt">27 E 800 183 212000 011[35] 10 E 800 183 212000 000[25] 10 E 807 183 212730 397[9] 10 E 807 183 212403 367[31]</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">35%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">25%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">9%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">31%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR><TR style="HEIGHT: 14px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 8pt">27 E 800 183 212000 011[35] 10 E 800 183 212000 000[1] 10 E 807 183 212730 397[44] 10 E 807 183 212403 367[20]</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">35%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">1%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">44%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">20%</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">100%</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AZ2</TD><TD>=SUM(AV2:AY2)</TD></TR><TR><TD>AZ3</TD><TD>=SUM(AV3:AY3)</TD></TR><TR><TD>AZ4</TD><TD>=SUM(AV4:AY4)</TD></TR><TR><TD>AZ5</TD><TD>=SUM(AV5:AY5)</TD></TR><TR><TD>AZ6</TD><TD>=SUM(AV6:AY6)</TD></TR><TR><TD>AZ7</TD><TD>=SUM(AV7:AY7)</TD></TR><TR><TD>AZ8</TD><TD>=SUM(AV8:AY8)</TD></TR><TR><TD>AZ9</TD><TD>=SUM(AV9:AY9)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Ron Coderre]

Here's one approach...Using your posted example...

AV2: =IF(COUNTIF(A2,"*
[*]*"),IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(
$A2,"[",REPT(" ",LEN($A2)),COLUMNS($B:B)),"]",REPT(" ",LEN($A2)),
COLUMNS($B:B)),LEN($A2),LEN($A2))),0)/100,1)

Copy AV2 and paste through AV9

AW2: =IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,"[",
REPT(" ",LEN($A2)),COLUMNS($AV:AW)),"]",REPT(" ",LEN($A2)),COLUMNS($AV:AW)),
LEN($A2),LEN($A2))),0)/100

Copy AW2 and paste down and across through AY9

Format the formula cells as Percent
Is that something you can work with?

 

[pagrender]

That worked perfectly Ron.

Thank you so much!

 

[AlphaFrog]

Excel Workbook
	AV	AW	AX	AY	AZ
1	One	Two	Three	Four	Total
2	100%	0%	0%	0%	100%
3	1%	99%	0%	0%	100%
4	76%	24%	0%	0%	100%
5	50%	30%	20%	0%	100%
6	50%	1%	49%	0%	100%
7	35%	25%	20%	20%	100%
8	35%	25%	9%	31%	100%
9	35%	1%	44%	20%	100%
...

Cell Formulas
Range	Formula
AV2	=IF(ISERROR(FIND("[",A2)),1,SUBSTITUTE(MID(A2,FIND("[",A2)+1,2),"]","")*0.01)
AW2	=IF(AV2=1,0,SUBSTITUTE(MID(A2,FIND("^^",SUBSTITUTE(A2,"[","^^",2))+1,3),"]","")*0.01)
AX2	=IF(SUM(AV2:AW2)=1,0,SUBSTITUTE(MID(A2,FIND("^^",SUBSTITUTE(A2,"[","^^",3))+1,3),"]","")*0.01)
AY2	=IF(SUM(AV2:AX2)=1,0,SUBSTITUTE(MID(A2,FIND("^^",SUBSTITUTE(A2,"[","^^",4))+1,3),"]","")*0.01)
AZ2	=SUM(AV2:AY2)

Copy each formula in row 2 down their column.

 

[pagrender]

That worked too AlphaFrog. Thanks!