# Sum of numbers with different prefix

#### foster5476

##### New Member
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 1 s-24 1 b-25 1 s-143-L 1 B-144-L 4 X Other info for me Other info for me 336 <--where I need the total

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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### 63falcondude

##### Well-known Member
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
Sheet1
Cell Formulas
RangeFormula
A9{=SUM(IFERROR(SUBSTITUTE(MID(B3:B7,FIND("-",B3:B7)+1,LEN(B3:B7)),"-L","")+0,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### foster5476

##### New Member
Thanks for the quick response let me give it a shot.

#### Tetra201

##### MrExcel MVP
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

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.

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

</tbody>
Sheet1

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

</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
Tetra when I put yours in im getting 0.

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

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
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
63falcondude I got yours to work also. Just not in Autocad yet if you have any pointers on inserting with other programs that may do the trick.

#### 63falcondude

##### Well-known Member
63falcondude I got yours to work also. Just not in Autocad yet if you have any pointers on inserting with other programs that may do the trick.

Sorry, I am not familiar with Autocad.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,681
Messages
5,838,780
Members
430,568
Latest member
bortey

### 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.

### Which adblocker are you using?

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

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