VBA Extract and sum numeric values from bracket

nicolastella

New Member
Joined
May 13, 2019
Messages
4
Hello, I had a code for this but I can find it anymore.
Basically, I have a sheet with a column of alphanumeric values. At the end of each string I have a decimal value between brackets, but also some text. I would like to extract the numeric value only, sum all the extracted values with the result on the next empty cell.
Returned value in B4, B8, B10.

Please see example below:


AB
1DESCRIPTIONTOT.
2sheet (2.340 sf)
3sheet (12.480 sf)
414.82
5plate (23.120 sf)
6plate (3.200 sf)
7plate (4.120 sf)
830.44
9bar (12.230 in)
1012.23

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,098
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Code:
Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Split(Replace(Cl.Value, "(", ""))(1)
      Next Cl
      Rng.Offset(Rng.Count, 1).Resize(1, 1).Value = MySum
      MySum = 0
   Next Rng
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Code:
Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Split(Replace(Cl.Value, "(", ""))(1)
      Next Cl
      Rng.Offset(Rng.Count, 1).Resize(1, 1).Value = MySum
      MySum = 0
   Next Rng
End Sub
Your code assumes the text before the open parenthesis will always be a single word... if it were two or more words (I am thinking of something like, say, "iron plate"), your code errors out. Here is a revision which handles that problem (as long as the opening parenthesis around the number is the first open parenthesis in the text) while compacting your MySum assignment code line slightly...
Code:
[table="width: 500"]
[tr]
	[td]Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Val(Mid(Cl, 1 + InStr(Cl, "(")))
      Next Cl
      Rng.Offset(Rng.Count, 1)(1).Value = MySum
      MySum = 0
   Next Rng
End Sub[/td]
[/tr]
[/table]
 

nicolastella

New Member
Joined
May 13, 2019
Messages
4
Thanks Fluff and Rick for your fast reply.
The Rick's code probably will work better for my scope, however, I forgot to mention that value in between brackets has first to be multiply with the number next to it (q.ty) and then add to total.
Please see sample below:

DESCRIPTIONQ.TYTOT.
SHEET METAL SS304 7GA (25.00 SF)250.00
SHEET METAL SS304 7GA (5.00 SF)15.00
SHEET METAL SS304 7GA (10.00 SF)315.00
70.00
SHEET METAL SS304 7GA (25.00 SF)250.00
SHEET METAL SS304 7GA (10.00 SF)110.00
60.00

<tbody>
</tbody>

Return values in column C. Partial values can be displayed or not, I just need to see the total (C4 , C7...)
If someone can help me with this one as well I will really appreciate.

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,098
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Code:
Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Val(Mid(Cl, 1 + InStr(Cl, "("))) * Cl.Offset(, 1)
      Next Cl
      Rng.Offset(Rng.Count, 2)(1).Value = MySum
      MySum = 0
   Next Rng
End Sub
 

nicolastella

New Member
Joined
May 13, 2019
Messages
4
This works perfectly!! You guys are the best.
Now, I used to run this code from Autodesk Inventor VBA linked to an Excel sheet rather than run it directly with Excel.
Doing this it give me a compile error (expect variable or procedure, not module) for the .SpecialCells(XlConstants).Areas.
I'm guessing this is happening because I didn't load the Excel object library. Can be this the case?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,098
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I've absolutely no idea, as I've never heard of AutoDesk Inventor.
So unfortunately cannot help with that.
 

nicolastella

New Member
Joined
May 13, 2019
Messages
4
Ok, I just changed from .SpecialCells(xlConstants) to .SpecialCells(xlCellTypeConstants) and it works from Inventor VBA.
Thanks all for your help,
Nicola
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,098
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,878
Messages
5,598,623
Members
414,248
Latest member
Tirali

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