Sum of numbers with different prefix

foster5476

New Member
Joined
Jan 3, 2017
Messages
7
Hello,

New to the forum and have read thru the tips before posting and hope my chart was loaded correctly. I also search for an answer to my question but found nothing.

Well I work out of Autocad but my tables are imported data from excel spreadsheets.

So what I am trying to do is get the my table to get the sum of the numbers with the S- or B- in front of the number and disregard the -L after the number.

I am not able to remove the prefix or suffix on the numbers. So is there a way to get only the number sum in a function form? Basically giving me 24+25+143+144=336

Thanks I hope this made sense.


LIST
QTY.MARK
1s-24
1b-25
1s-143-L
1B-144-L
4X
Other info for me
Other info for me
336 <--where I need the total

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
This worked for your example given. Are there any instances where this will not work?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">LIST</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">QTY.</td><td style=";">MARK</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style=";">s-24</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style=";">b-25</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style=";">s-143-L</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1</td><td style=";">B-144-L</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">4</td><td style=";">X</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">336</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A9</th><td style="text-align:left">{=SUM(<font color="Blue">IFERROR(<font color="Red">SUBSTITUTE(<font color="Green">MID(<font color="Purple">B3:B7,FIND(<font color="Teal">"-",B3:B7</font>)+1,LEN(<font color="Teal">B3:B7</font>)</font>),"-L",""</font>)+0,""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IFERROR(--MID(SUBSTITUTE(B3:B7,"-L",""),3,99),0))
 

foster5476

New Member
Joined
Jan 3, 2017
Messages
7

ADVERTISEMENT

When I post it in my Autocadd I get ####

I tried in my excel 2013 also and for some reason ctrl, shift, enter doesn't work but was able to insert it keeping the format. It came out looking just like how you posted with the colors and the brackets. Am I doing something wrong? Im not work a lot in excel if there is something I need to tinker with.

Thanks in advance.

This worked for your example given. Are there any instances where this will not work?

Excel 2010
AB
1LIST
2QTY.MARK
31s-24
41b-25
51s-143-L
61B-144-L
74X
8
9336

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
A9{=SUM(IFERROR(SUBSTITUTE(MID(B3:B7,FIND("-",B3:B7)+1,LEN(B3:B7)),"-L","")+0,""))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

foster5476

New Member
Joined
Jan 3, 2017
Messages
7

ADVERTISEMENT

Sorry I messed up inputting the formula.

My ctrl shift enter isnt working correctly I guess but I got it.

Thank I really appreciate it.

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IFERROR(--MID(SUBSTITUTE(B3:B7,"-L",""),3,99),0))
 

foster5476

New Member
Joined
Jan 3, 2017
Messages
7
Is there a reason why it wouldnt work in Autocadd and it does in excel? Must be the way im putting in the equation????
 

foster5476

New Member
Joined
Jan 3, 2017
Messages
7
63falcondude I got yours to work also. Just not in Autocad yet :confused: if you have any pointers on inserting with other programs that may do the trick.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,858
Members
414,408
Latest member
macroSmith

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
Top