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

Donbozone

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Give this version a try...
VBA Code:
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

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. (y)

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.

VBA Code:
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
DEFGRSTUAFAGAHAIATAUAVAWBHBIBJ
12subotanedjeljasubotanedjeljasubotanedjeljasubotanedjeljasubotaCount WE
13CNCN0
14A6CNA72
15A6A62
16B6A7CN8A54
17BBA7CNA72
18A7B5A63
19CNA7CN1
Sheet2
Cell Formulas
RangeFormula
BJ13:BJ19BJ13=Count_WE(D$12:BI$12,D13:BI13)
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,825
Messages
5,544,545
Members
410,619
Latest member
gregor222
Top