Results 1 to 6 of 6

Thread: extracting number from text string and doing calculations
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default extracting number from text string and doing calculations

    I have to extract the left most amount and the number of days from the below attached text string and perform mathematical operations depending on the number of days.

    Condition
    $280 for days 1 through 6, $0 afterwards
    $240 for days 1 through 3, $0 afterwards
    $290 for days 1 through 4, $10 afterwards
    $280 for days 1 through 5, $10 afterwards

    Final sum for all the below rows to be calculated according to logic.
    Amount*4 ( If number of days if >=4)
    Amount*number of days( If number of days is less than 4)

    Example
    1) The first sum should be 280*4
    2) second sum should be 240*3

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: extracting number from text string and doing calculations

    looks like an assignment to me !
    what have you tried so far AND how did you arrive at The first sum should be 280*4 ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extracting number from text string and doing calculations

    I was able to extract the leftmost amount value using the LEFT Function.
    the problem comes when i have to extract the day value, i.e 6 in the first case, 3 in the 2nd case.

    how did you arrive at The first sum should be 280*4 ??
    because as the logic states we have to multiply with 4 if the number of days is >=4, in the first case it's 6 so we have to multiply the amount with 4.

  4. #4
    Board Regular
    Join Date
    May 2015
    Location
    Kolkata, India
    Posts
    506
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extracting number from text string and doing calculations

    Something like this:

    AB
    1$280 for days 1 through 6, $0 afterwards1120
    2$240 for days 1 through 3, $0 afterwards960
    3$290 for days 1 through 4, $10 afterwards1160
    4$280 for days 1 through 5, $10 afterwards1120

    Sheet8



    Worksheet Formulas
    CellFormula
    B1=(MID(A1,2,FIND(" ",A1)-2))*IF(MID(A1,SEARCH("through",A1)+8,1)>=4,4,MID(A1,SEARCH("through",A1)+8,1))

    Regards,
    Nishant Ghosh

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extracting number from text string and doing calculations

    I agree w/Michael: it sounds like a class assignment. Are the four sums: 1120, 720, 1160, and 1120? Try it by hand and see what you should get.

    I don't agree w/your second number and suspect it's because you search returned a character 3 versus a number 3.

    BTW, you could also look for the comma.
    Last edited by kweaver; Sep 17th, 2019 at 12:27 AM.

  6. #6
    Board Regular
    Join Date
    May 2015
    Location
    Kolkata, India
    Posts
    506
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extracting number from text string and doing calculations

    Or,

    AB
    1$280 for days 1 through 6, $0 afterwards1120
    2$240 for days 1 through 3, $0 afterwards960
    3$290 for days 1 through 4, $10 afterwards1160
    4$280 for days 1 through 5, $10 afterwards1120

    Sheet8



    Worksheet Formulas
    CellFormula
    B1=MID(A1,2,FIND(" ",A1)-2)*IF(MID(A1,FIND(",",A1)-1,1)>=4,4,MID(A1,FIND(",",A1)-1,1))

    Regards,
    Nishant Ghosh

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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