Splitting a String into the Numbers of Male and Female and Sum them up

[lkerwei]

Hi, I seek for help with splitting a string into numbers of male and female where they're going attend a bakery class.
I have found a code from website but it doesn't work with my excel because it doesn't refer the string that contains the bakery classes

Please Login or Register  to view this content.

I understand the code that it search the space before the number, and it sum up the values, but the problem is the cell contains different bakery classes and it need to be sum with their own class only.
I have attach an example for better understanding.

Thank you.

[jindon]

1)
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



2) UDF

Please Login or Register  to view this content.

Use in cell
F5:
=IFERROR(SumNumbers($E5,F$3&"("),"")
Copy right/down

[Phuocam]

Sorry, I cannot upload the code because of a firewall.

[lkerwei]

You guys make my day, thank you very much and appreciate. Jindon and Phuocam

[jindon]

You welcome and thanks for the code tags.

[Rick Rothstein]

Sorry, I cannot upload the code because of a firewall.

I had a problem also!!! Something about "SQL injection" (whatever that is).

Here is the write-up that I wanted to introduce the code...

Here is another UDF that you can consider (see attached file for the actual code). The setup is slightly different than for the UDF that jindon posted. You pass it the text to be evaluated and the text item to sum up. So for my function, you would place this in cell F5...

=SumNumbers($E5,F$3)

and then copy it down and across.

[AliGW]

FYI - if your code contains anything that Sucuri detects as HTML, it will be rejected.

[Rick Rothstein]

FYI - if your code contains anything that Sucuri detects as HTML, it will be rejected.

As far as I can tell, there was nothing HTML-ish about the code I tried to post. Here is a link to what I tried to post in a text file shared on DropBox...

https://www.dropbox.com/s/ar72yap14d...osted.txt?dl=0

Can you tell me what part of my code offended Sucuri?

[AliGW]

Unfortunately not, Rick, but I tried to post it myself and got the same issue.

It's just one of those things I'm afraid - there's nothing we can do about it unfortunately. I just posted so that you'd be aware that it's not an unknown issue.

[AliGW]

Something in the red section is throwing the error:

Please Login or Register  to view this content.

[Rick Rothstein]

Thanks for letting me know.

[AliGW]

See above - I can isolate the section, but not the actual word.

[Rick Rothstein]

See above - I can isolate the section, but not the actual word.

The Sucuri reason for blocking the message was "SQL injection was detected and blocked." Since I know absolutely nothing about SQL, I have no way of figuring out what part of my code was the problem. To me, everything I posted was straightforward VBA code. Maybe the problem was the particular arrangement of VBA keywords that my code used as opposed to just a single keyword.

[AliGW]

Yup - we've never really got to the bottom of it, but we know HTML tags will trigger it. In this case, I have no idea.

[Marc L]

Hi !

Securi does not like for example a VBA codeline with two consecutive Replace( but accepts Replace$(Replace$( …

[Marc L]

I already wrote several times previous Securi tip within this forum …

Or from Rick's code just inserting a space between the two Replace statements
as it no matters 'cause VBA will automatically delete this added space :

Function SumNumbers(Txt As String, Itm As String) As Variant
  SumNumbers = ""
  On Error GoTo Whoops
  SumNumbers = Evaluate(Replace(Application.Trim(Replace( Replace(Split(Split(Txt, Itm & "(")(1), ")")(0), "M", " "), "F", " ")), " ", "+"))
Whoops:
End Function 

[Rick Rothstein]

Securi does not like for example a VBA codeline with two consecutive Replace( but accepts Replace$(Replace$( …

[INDENT]Or from Rick's code just inserting a space between the two Replace statements

Thanks for the tip!

[Marc L]

You're welcome and thanks for this unexpected rep' added !