Extraction of multiple numbers within a long string in excel vba

  • Hello everyone,


    i have a string that looks like this: [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]


    I am trying to get every number between the "@" and "," (19, 27, 27, 27, 6, 73...) and multiply them with 0.9.


    This is my approach so far but it only gets me to 19. Not sure how to get through the whole string.



    What would you guys suggest is the best way to do this?


    Thank you!

  • Go to Best Answer
  • Replace the @ with commas, then use Split with a comma as the delimiter. Then loop through every other item in the resulting array (using Step 2). It's not clear to me what output you are expecting.


    You could also achieve this with a formula.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi and Welcome to the Forum :)


    You could test the following code

    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi and Welcome to the Forum :)


    You could test the following code

    Hope this will help :)

    Awesome, this is pretty much exactly what i wanted. Thank you so much!

  • Glad the macro is helping you out :)


    To transform it into a UDF is not very difficult ... but this would require a clarification :


    once every element is multiplied by 0.9, do you need to get the Sum of all these elements ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi again,


    Below is your revised UDF :

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Absolutely LOVE it !!! <3


    Would you mind attaching a sample file ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Just out of curiosity ... ;)


    - What is the Len(A1) ?

    - Is it more than 32'767 characters ???

    - What is your Cell Limit ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    • New
    • Best Answer

    Hi,


    Thanks for the sample ...


    Following modification to be tested:

    Hope some progress has been made ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Should we be heading in the right direction ...


    Next step would to define exactly what you actually mean by :


    "multiplied with 0.9 rounded up or down depending on the result" ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

  • Yes for sure, its working now! The rounding is already perfect as it is. Thank you so much!

  • Thanks a lot for your Thanks :)


    Very pleased to hear you have managed to fix your initial problem :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hey Carim,


    hope you can help me again here.


    What would be the easiest way to be able to change the multiplier "0.9" through a textbox manually? I was thinking of passing the multiplier through the function call but i dont even know if thats possible or if there is a better way.

  • HI,


    Below is your version 2 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!