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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
Here is a UDF (user defined function) that you can use for any range (either one or two dimensional)...
VBA Code:
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.
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Hi. Thank you for solution. It works, but it turned out that I need to set a condition.

The range of cells in my rows presents actually days in month, so I would have to avoid codes on working days (Mon-Fri) and to use the function only to summarize codes in columns Saturday and Sunday.

What makes things more difficult is a fact that each day covers two columns, but only left column contain header and it is merged with the right one. So for example, columns (a) and (b) are for Monday. "Monday" is written in a1 and merged with b1, so my second column actually does not have a header.

If it could help, these columns are colorized, so it might be set as a condition instead of column header..

Is there any way to solve this?.

Thank you.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
Merged cells always cause problems and are best avoided. But, you are already set up with them so let's go from there. I think we will need to see a copy of your workbook (obfuscate any sensitive data) in order to see exactly how you are set up. I would suggest posting it to DropBox as that is a known safe site. We may have more questions, but until we see exactly what we need to deal with, we won't know what to ask as of yet.
 

Peter_SSs

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

ADVERTISEMENT

I do have some more questions already:

1. In all your samples, the 'number' is at the right of the text. Is that always the case?

2. In all your examples the 'number' is a single digit. Is that always the case?

3. Can you confirm that it is only row 1 that has merged cells? That is A1 & B1 are merged but A2 & B2 are not merged and may contain 2 separate values?

4. Does your version of Excel 365 contain the new dynamic array functions (for example =UNIQUE() )?
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Merged cells always cause problems and are best avoided. But, you are already set up with them so let's go from there. I think we will need to see a copy of your workbook (obfuscate any sensitive data) in order to see exactly how you are set up. I would suggest posting it to DropBox as that is a known safe site. We may have more questions, but until we see exactly what we need to deal with, we won't know what to ask as of yet.
Yes, Im aware of that, but it is the template that arrives from client each month, so even if I change this one (what would be the best solution), I will receive the same one next month (with merged cels in header). What I find as a solution could be extention of the vba code in order to make formula work with more ranges (a1:d1,h1:k1,...). That could help.
 

Donbozone

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

ADVERTISEMENT

I do have some more questions already:

1. In all your samples, the 'number' is at the right of the text. Is that always the case?

2. In all your examples the 'number' is a single digit. Is that always the case?

3. Can you confirm that it is only row 1 that has merged cells? That is A1 & B1 are merged but A2 & B2 are not merged and may contain 2 separate values?

4. Does your version of Excel 365 contain the new dynamic array functions (for example =UNIQUE() )?
1 yes
2 yes
3 yes, only headers are merged
4 i will check this (Im on mobile at the moment)

Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,814
Office Version
  1. 365
Platform
  1. Windows
Thanks for the additional information. Does this UDF do what you want?

VBA Code:
Function Sum_WE(rDays As Range, rVals As Range) As Long
  Dim c As Range
  
  For Each c In rVals
    If Left(UCase(Cells(1, c.Column - ((c.Column - rVals.Column) Mod 2)).Value), 1) = "S" 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
1MondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundaySum Weekend
2CE4G2CXACBV8ABVC5AB2CC722
3XXYY0
4B0A99
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
This is it, well done.

Just one more small tiny adjustment.

Since template file is in Croatian and instead of Saturday and Sunday I have "subota" and "nedjelja"..and ok, for Saturday it works because UDF checks first letter. Can it be adjusted to check "S" OR "N"? There is no other day starting with N, so result should be correct.

Thanks a lot.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,814
Office Version
  1. 365
Platform
  1. Windows
instead of Saturday and Sunday I have "subota" and "nedjelja"..and ok, for Saturday it works because UDF checks first letter. Can it be adjusted to check "S" OR "N"? There is no other day starting with N
Try
VBA Code:
If InStr(1, "NS", Left(Cells(1, c.Column - ((c.Column - rVals.Column) Mod 2)).Value, 1), 1) > 0 And IsNumeric(Right(c.Value, 1)) Then Sum_WE = Sum_WE + Right(c.Value, 1)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,835
Messages
5,544,590
Members
410,621
Latest member
S Oberlander
Top