Extract numbers from text string (within row range) and sum

[Donbozone]

Hi.

I couldnt find solution for a simple problem at first sight.

In a short, I have a row with some codes in cells. These codes were made by letters and digits.

For example:

CE4, BV8, G2, CX, AC - these are codes in a separate cells let say in a range (a1:a5)

What I need is a function or vba code which is going to extract digits from each cell and then make a sum of extracted values.

In this case result would be: 4+8+2=14

Of course, I have many rows, so formula in a6 would be best solution, so I could just pull it down to the last row.

Thanks for any help.

 

[Rick Rothstein]

Was it changed in a way not to make sum, but to count codes that have digit inside and belong to non-working days?

Yes. It works on my test which probably means there is something about your set up that we are not duplicating here. The only suggestion I can give to you is to post a copy of your workbook (obfuscate any sensitive data) to (my preference) DropBox so that we can test our code on your actual data directly.

 

[Donbozone]

Here it is

Template.xlsm

Shared with Dropbox

www.dropbox.com

 

[Rick Rothstein]

Your example file only shows Saturday and Sunday dates... based on what you posted in Message #13, I thought we had to ignore weekday dates in your table. Is your example file actually what you are working with? If so and there are no weekday dates to worry about, we can modify our codes slightly..

 

[Donbozone]

Your example file only shows Saturday and Sunday dates... based on what you posted in Message #13, I thought we had to ignore weekday dates in your table. Is your example file actually what you are working with? If so and there are no weekday dates to worry about, we can modify our codes slightly..

Sorry, workdays are hidden just to have a clear view of what result I should get. You can unhide it.

That is exactly the file that I work in, rows and colums were not changed. I have just deleted some people names.

 

[Rick Rothstein]

Give this version a try...

Function Sum_WE(rVals As Range) As Long
  Dim c As Range
  For Each c In rVals
    If Len(c.Value) Then
      If Intersect(c.EntireColumn, Rows(12)).MergeArea(1).Value Like "[SsNn][UuEe]*" Then Sum_WE = Sum_WE - (c.Value Like "*#")
    End If
  Next c
End Function

 

[Donbozone]

Using the same formula format BJ13=Sum_WE(D$12:BI$12,D13:BI13) I get result "values".

If I'm right, this formula supposed to sum values from codes, as per my initial request, but meanwhile the request has changed (not by my will), so we should maybe first rename the function to Count_WE and adjust it just to count codes that contains digit and only for non-working days.

In my template I put manually expected results in column BK.

Thanks

 

[Peter_SSs]

By the way Peter, you do not need the first argument in your function as you seem to no longer be using it.

That was a mistake on my part - see below - but thanks for pointing it out. I also take your point about other ways to write what I had done.

Using the same formula format BJ13=Sum_WE(D$12:BI$12,D13:BI13) I get result "values".

You appear to have been using Rick's suggested function incorrectly. His latest function only has one argument so it would be used like this
=Sum_WE(D13:BI13)

However, I would strongly recommend re-introduction of the header argument that was dropped. As it stands, if any rows are added or deleted above row 12, the function will return incorrect results with no warning. So here I have re-named the function to indicate a count, re-introduced the header argument and tweaked the code a little. See how this one goes.

Function Count_WE(rDays As Range, rVals As Range) As Long
  Dim c As Range
  
  For Each c In rVals
    If c.Value Like "*#" Then
      If Cells(rDays.Row, c.Column).MergeArea(1).Value Like "[SsNn][UuEe]*" Then Count_WE = Count_WE + 1
    End If
  Next c
End Function

Donbozone 2020-04-05 1.xlsm
	D	E	F	G											R	S	T	U											AF	AG	AH	AI											AT	AU	AV	AW											BH	BI	BJ
12	subota		nedjelja												subota		nedjelja												subota		nedjelja												subota		nedjelja												subota		Count WE
13			CN															CN																																									0
14		A6													CN															A7																													2
15																														A6														A6															2
16		B6														A7															CN8																											A5	4
17		BB														A7															CN																											A7	2
18		A7																												B5														A6															3
19				CN																										A7															CN														1
Sheet2

Cell Formulas
Range	Formula
BJ13:BJ19	BJ13	=Count_WE(D$12:BI$12,D13:BI13)

 

[Donbozone]

This works like a charm.

You guys are awesome. Many thanks for your help, and I apologise once again for changing my request after you already spent some time to make something other.

This solution works, but I have also made to make some workaround with CONCAT and LEN. Basically, I have concatenated ranges related to weekends and then with LEN and SUBSTITUTE counted number of digits in string:

BJ13=SUM(LEN(CONCAT(D13:G13,R13:U13,AF13:AI13,AT13:AW13,BH13:BI13))-LEN(SUBSTITUTE(CONCAT(D13:G13,R13:U13,AF13:AI13,AT13:AW13,BH13:BI13),{1,2,3,4,5,6,7,8,9,0},)))

This one works, but as I said, it is just a workaround, because CONCAT range is not dynamic, so each next month would have to be adjusted.

UDF is much better solution.

Best regards

 

[Peter_SSs]

This works like a charm.

Good to hear! Thanks for the follow-up.

 

[OZKAN24]

Here is a UDF (user defined function) that you can use for any range (either one or two dimensional)...

Function NumSum(Rng As Range) As Double
  Dim X As Long, Cell As Range, Combined As String
  For Each Cell In Rng
    Combined = Combined & Cell.Value
  Next
  For X = 1 To Len(Combined)
    If Mid(Combined, X, 1) Like "[!0-9.]" Then Mid(Combined, X) = " "
  Next
  NumSum = Evaluate(Replace(Application.Trim(Combined), " ", "+"))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumSum just like it was a built-in Excel function. For example,

=NumSum(A1:A5)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Hi.
I used this formula to sum different cells containing numbers mixed with text.
It works. But it does not subtract negative numbers from the total.
Can you help me ?
Thanks in advance.