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.
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
I cant believe Im making such a mistakes..

These are days in Croatian: subota, nedjelja, ponedjeljak, utorak, srijeda, četvrtak and petak. I forgot that both, Saturday and Wednesday starts with (S)
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,911
Office Version
  1. 365
Platform
  1. Windows
I cant believe Im making such a mistakes..

These are days in Croatian: subota, nedjelja, ponedjeljak, utorak, srijeda, četvrtak and petak. I forgot that both, Saturday and Wednesday starts with (S)
VBA Code:
If InStr(1, "NESU", Left(Cells(1, c.Column - ((c.Column - rVals.Column) Mod 2)).Value, 2), 1) > 0 And IsNumeric(Right(c.Value, 1)) Then Sum_WE = Sum_WE + Right(c.Value, 1)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,911
Office Version
  1. 365
Platform
  1. Windows
Now it summarize all days..
Not for me.

VBA Code:
Function Sum_WE(rDays As Range, rVals As Range) As Long
  Dim c As Range
  
  For Each c In rVals
    If InStr(1, "NESU", Left(Cells(1, c.Column - ((c.Column - rVals.Column) Mod 2)).Value, 2), 1) > 0 And IsNumeric(Right(c.Value, 1)) Then Sum_WE = Sum_WE + Right(c.Value, 1)
  Next c
End Function

Donbozone 2020-04-05 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1ponedjeljakutoraksrijedačetvrtakpetaksubotanedjeljaponedjeljakutoraksrijedačetvrtakpetaksubotanedjeljaSum Weekend
2CE4BV6G2CXACBV8ABVC5AB2CC722
3XXYY0
4AA4AA4B0A99
Sheet1
Cell Formulas
RangeFormula
AC2:AC4AC2=Sum_WE(A$1:AB$1,A2:AB2)
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I will check once again, but seems not important at the moment.

My colleague has just informed me that she actually needs to count codes that have digits in string and belong to weekend days (subota, nedjelja).

In your example in AC2 instead of sum (result 22), it supposed to be count (result 4).

I really apologize for this inconvenience.
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
309
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@Peter,

I can not change your UDF, because the days, in my sheet, are in one cell (not in two).
Can you please help me.
 

Rick Rothstein

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

ADVERTISEMENT

VBA Code:
If InStr(1, "NESU", Left(Cells(1, c.Column - ((c.Column - rVals.Column) Mod 2)).Value, 2), 1) > 0 And IsNumeric(Right(c.Value, 1)) Then Sum_WE = Sum_WE + Right(c.Value, 1)
Here is a shorter way to write the above code line...
VBA Code:
If Intersect(c.EntireColumn, Rows(1)).MergeArea(1).Value Like "[SsNn][UuEe]*" Then Sum_WE = Sum_WE + Val(StrReverse(c.Value))
By the way Peter, you do not need the first argument in your function as you seem to no longer be using it.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,435
Office Version
  1. 2010
Platform
  1. Windows
My colleague has just informed me that she actually needs to count codes that have digits in string and belong to weekend days (subota, nedjelja).
Does this modification to Peter's code work for you (note I have shortened his If..Then statement using the technique I employed in Message #17)...
VBA Code:
Function Sum_WE(rVals As Range) As Long
  Dim c As Range
  For Each c In rVals
    If Intersect(c.EntireColumn, Rows(1)).MergeArea(1).Value Like "[SsNn][UuEe]*" Then Sum_WE = Sum_WE - (c.Value Like "*#")
  Next c
End Function
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
No, it seems it does not work.

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

Watch MrExcel Video

Forum statistics

Threads
1,114,510
Messages
5,548,488
Members
410,840
Latest member
Kar3ousse
Top