Hello

Guys I am stuck in extracting the correct numerical values from strings with different values. For example a value of 13 days requires me to set it at left(5,2) while 1 day requires me to choose left(4,2) and even in the middle some string require 5 mid values because of string "days" while others require 4 mid values because the string reads "day"

0 Days, 1 Hour, 38 Minutes, 2 Seconds

0 Days, 3 Hours, 15 Minutes, 58 Seconds

0 Days, 1 Hour, 44 Minutes, 44 Seconds

0 Days, 0 Hours, 58 Minutes, 49 Seconds

0 Days, 0 Hours, 29 Minutes, 26 Seconds

0 Days, 23 Hours, 32 Minutes, 1 Second

0 Days, 0 Hours, 38 Minutes, 0 Seconds

1 Day, 0 Hours, 59 Minutes, 2 Seconds

0 Days, 2 Hours, 56 Minutes, 19 Seconds

0 Days, 2 Hours, 1 Minute, 2 Seconds

1 Day, 1 Hour, 16 Minutes, 24 Seconds

0 Days, 3 Hours, 3 Minutes, 38 Seconds

0 Days, 6 Hours, 11 Minutes, 35 Seconds

0 Days, 1 Hour, 30 Minutes, 38 Seconds

0 Days, 2 Hours, 5 Minutes, 39 Seconds

0 Days, 2 Hours, 16 Minutes, 46 Seconds

I believe that I provided solutions to both of your issues in response #4 of your previous thread. https://www.computing.net/answers/o...

I have not tested them on your latest examples, but I see no reason why they wouldn't work. I can can test them later, but you should try them on your own as a learning experience.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

I just adapted my suggestion from your previous thread and as far as I can tell it works fine. Using the following data, I got the corresponding results:

A B 1 0 Days, 1 Hour, 38 Minutes, 2 Seconds 1.63 Hours 2 1 Day, 3 Hours, 15 Minutes, 58 Seconds 27.27 Hours 3 13 Days, 13 Hours, 1 Minute, 5 Seconds 325.02 Hours 4 5 Days, 1 Hour, 0 Minutes, 7 Seconds 121.00 HoursIf you get stuck, I'll post the formula I built, but I suggest that you try it on your own first.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Coming in late on this, but If you do not want: 0 Days, 1 Hour, 38 Minutes, 2 Seconds

how do you want it to appear?MIKE

Mike, My guess is that this question is related to his previous thread, to which you responded before going off to enjoy your holidays. Check out his response to you and my subsequent suggestion.

https://www.computing.net/answers/o...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

My guess is that this question is related to his previous threadKinda figured that, but there are other ways to configure Chips formula so the return values are formatted differently.

There was one person who wanted the return values as percentages.I have a few different formulas that do the same thing, only differently. ;-)

MIKE

Hi guys. Im sorry for reposting. MMconaghy solution was right for calculating the aging ( business days only). And DerbyDad03 helped in extracting the numerical values from a text string. I made a mistake asking again. But because it was so advanced to me I did not understand. I still can't figure the correct formula DerbyDad03. Because I don't get the how to use delimeter in a formula correctly. Can u please provide me with a formula DerbyDad03 thank you.

As Mike mentioned, it would help if we knew exactly what output you are looking for. Keep in mind that we can't see your workbook from where we are sitting nor do we know anything about your work process.

Give us a couple of examples of your input and the exact output that you are expecting from each input.

We'll see what we can do to help.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History