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>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 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))
 
Upvote 0
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>
 
Upvote 0
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))
 
Upvote 0
Is there a reason why it wouldnt work in Autocadd and it does in excel? Must be the way im putting in the equation????
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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