+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Registered User
    Join Date
    07-10-2019
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    28

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

    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.
    Attached Files Attached Files
    Last edited by lkerwei; 08-17-2019 at 01:01 AM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

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

    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
    Last edited by jindon; 08-17-2019 at 12:19 AM.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

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

    Sorry, I cannot upload the code because of a firewall.
    Attached Files Attached Files
    Last edited by Phuocam; 08-17-2019 at 01:25 AM.

  4. #4
    Registered User
    Join Date
    07-10-2019
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    28

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

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

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

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

    You welcome and thanks for the code tags.

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

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

    Quote Originally Posted by Phuocam View Post
    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.
    Attached Files Attached Files
    Last edited by Rick Rothstein; 08-17-2019 at 01:39 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

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

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

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

    Quote Originally Posted by AliGW View Post
    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?
    Last edited by Rick Rothstein; 08-17-2019 at 02:02 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

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

    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.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

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

    Something in the red section is throwing the error:

    Please Login or Register  to view this content.

  11. #11
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

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

    Thanks for letting me know.
    Last edited by AliGW; 08-17-2019 at 02:07 AM. Reason: Please don't quote unnecessarily!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

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

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

  13. #13
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

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

    Quote Originally Posted by AliGW View Post
    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.
    Last edited by AliGW; 08-17-2019 at 02:13 AM. Reason: Please don't quote unnecessarily!

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

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

    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.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi !

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

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    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 :

    PHP Code: 
    Function SumNumbers(Txt As StringItm As String) As Variant
      SumNumbers 
    ""
      
    On Error GoTo Whoops
      SumNumbers 
    Evaluate(Replace(Application.Trim(ReplaceReplace(Split(Split(TxtItm "(")(1), ")")(0), "M"" "), "F"" ")), " ""+"))
    Whoops:
    End Function 

  17. #17
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

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

    Quote Originally Posted by Marc L View Post
    Securi does not like for example a VBA codeline with two consecutive Replace( but accepts Replace$(Replace$(
    Quote Originally Posted by Marc L View Post
    [INDENT]Or from Rick's code just inserting a space between the two Replace statements
    Thanks for the tip!

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Separate Male from Female
    By Simply_Me in forum Excel General
    Replies: 8
    Last Post: 03-28-2019, 07:03 AM
  2. Automatically adding Mr or Ms to male/female
    By TTej in forum Excel General
    Replies: 7
    Last Post: 10-03-2017, 11:49 AM
  3. PT Slab male and female
    By jaydev in forum Excel General
    Replies: 7
    Last Post: 01-28-2017, 12:12 PM
  4. Getting totals for all male and all female
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-09-2014, 09:32 PM
  5. Male or Female Return Value
    By Doogster in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2014, 04:19 AM
  6. Excel Male / Female Column
    By ringrim in forum Excel General
    Replies: 5
    Last Post: 08-13-2008, 05:28 PM
  7. IF male or female IF age is in range...THEN...
    By mtmagent in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2006, 03:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1