Find numbers in Brackets

P

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
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>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Here's one approach...Using your posted example...

Code: 
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

Code: 
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?
 
Last edited:
Upvote 0
Excel Workbook
AVAWAXAYAZ
1OneTwoThreeFourTotal
2100%0%0%0%100%
31%99%0%0%100%
476%24%0%0%100%
550%30%20%0%100%
650%1%49%0%100%
735%25%20%20%100%
835%25%9%31%100%
935%1%44%20%100%
...
Cell Formulas
RangeFormula
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
431
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
835
gvsumich
G
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
351
esben
E

Forum statistics

Threads
1,215,064
Messages
6,122,942
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top