Sum the numerical values following each instance of specific text in one cell

[danielb]

I need help with an iteration to my existing formula where I need to add certain values within the same cell based on specific criteria.
Currently, I have cell H2 that has a plain text value like this:
GroupAct: Group Actuarial - Division~28*Group Sales Support - Division~2*Group Systems - Division~1*Group Underwriting - Division~4*Individual Life - Division~1*IT - Business Unit~1*--~3
[This is showing access group names, then a colon, then the divisions that are in that access group. The divisions are formatted by name, then a tilde, then how many people are in that division, then an asterisk, and repeat]

I then have an IF statement in cell I2 that looks for a specific text string and returns Yes or leaves the value blank:

=IF(ISNUMBER(SEARCH(": Group",H2)),"Yes",IF(ISNUMBER(SEARCH("*Group",H2)),"Yes",""))

So this returns Yes in column I for column H’s value

I am now being asked now to sum the numerical values following each instance of this specific text string being found in H2. So, in the above example:
- Find ‘: Group’ and the 28 after it
- Find the first ‘*Group’ and the 2 after it
- Find the second ‘*Group’ and the 1 after it
- Find the third ‘*Group’ and the 4 after it
- Add these values up to 35 and put that in cell I2 instead of just Yes.

I have looked at little at SUMPRODUCT, but I am not sure how that works as an array. Any help is appreciated to get me on the right track. Thanks!

 

[Rick Rothstein]

If you can use a VBA solution, here is a UDF (user defined function) that should do what you want providing your single example is representative of the text that needs to be processed...

Function GroupAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Group", , vbTextCompare)
  For X = 0 To UBound(Groups)
    GroupAdd = GroupAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
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 GroupAdd just like it was a built-in Excel function. For example,

=GroupAdd(A1)

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.

 

[danielb]

Thanks Rick for your quick reply! Is there a way to code this within VBA where I could run a macro myself and put focus in I2 get the result from H2 without a UDF and saving the file as a macro enabled workbook? The client I am working with will be sending me multiple xls files each week, so it may not be efficient for me to save a UDF on each file and a save it as an xlsm; instead, what I would like is when I get the xls from the client and I run a macro saved in my PERSONAL.XLSB to do the functions or formulas to get the added up counts. I can also have the macro save the formula result as text and present the client with an xls file with the new data as text, which is what they prefer (no formulas in the final version). They also are 'gun shy' about enabling macros due to malicious email attachments in phishing attempts.

Sorry I didn't think to include this information in the initial post .

 

[danielb]

Rick –
I figured out how to put the function in my PERSONAL.XLSB file and I use the formula “=PERSONAL.XLSB!GroupAdd.GroupAdd(H2)” and it returns a count...thank you so much for your help so far! A few follow-up questions:

1. Does the function you wrote above look just for instances of “Group” or for “: Group” and “*Group”? There will be times where an access group name will be something like “*Individual Claims Group” and it should not count that. The function should only add up the numbers after the tilde if “: Group” or “*Group” precedes it.
2. I need numerous other functions to look for other text strings…another example would be one that looks for “: Individual” and “*Individual”. So, I figured I would create another function called IndivAdd and I replaced certain parts of your function to this:

Function IndivAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Individual", , vbTextCompare)
  For X = 0 To UBound(Groups)
    IndivAdd = IndivAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
End Function

I then use “=PERSONAL.XLSB!IndivAdd.IndivAdd(H2)” and this returns a count, but it is not correct. Like for the above example I posted, it returns a count of 29 and it should return 1.Maybe the answer to (1) will help with this too, but I wanted to mention this.

Any help you can provide would be much appreciated. Thanks!

 

[danielb]

Rick, can you help provide any other assistance with the questions on my last post? Does anyone else have any other suggestions? Thanks.

 

[danielb]

I'm not sure if replying again 'bumps' this thread in the forums, but I am still in need of some assistance. I have done some online searching, but I still cannot figure the answers to my questions on the March 29th post. Any help is appreciated. Thanks.

 

[Sanjeev1976]

I'm not sure if replying again 'bumps' this thread in the forums, but I am still in need of some assistance. I have done some online searching, but I still cannot figure the answers to my questions on the March 29th post. Any help is appreciated. Thanks.

Try:

Function GroupAdd(Text As String) As Double
Dim X As Long, Groups() As String
Groups = Split(Text, "Individual", , vbTextCompare)
For X = 1 To UBound(Groups)
GroupAdd = GroupAdd + Val(Split(Groups(X) & "~", "~")(1))
Next
End Function

 

[danielb]

Sanjeev, I apologize for the lateness of my reply. Your suggestion worked perfectly...I have created numerous functions from your suggestion to look for the different text strings and count up the numbers and they work great; however, as I was implementing it, I realized that I need help with another 'wrinkle'...I have a need for some of these functions to look for 2 strings vs. 1.

So for example, I have one of the functions called 'AuditAdd' and it currently looks like the below code. In addition to finding the string "Audit" and also need it to find the string "Risk & Compliance" and add all the counts from both strings. Can you help with modifying the code to find 2 string values vs. just 1? Thanks!

Function AuditAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Audit", , vbTextCompare)
  For X = 1 To UBound(Groups)
    AuditAdd = AuditAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
End Function

 

[Sanjeev1976]

Thanks for your feedback.

Try :

Function AuditAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Audit", , vbTextCompare)
  For X = 1 To UBound(Groups)
    AuditAdd = AuditAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
  Groups = Split(Text, "Risk & Compliance", , vbTextCompare)
  For X = 1 To UBound(Groups)
    AuditAdd = AuditAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
End Function

 

[danielb]

Sanjeev, that worked like a champ! Thank you so much for your help!