Solved Extracting numbers from different types of text input.

December 29, 2019 at 23:44:56
Specs: Windows 7
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


See More: Extracting numbers from different types of text input.

Report •

#1
December 30, 2019 at 03:50:58
✔ Best Answer
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


Report •

#2
December 30, 2019 at 06:05:59
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 Hours

If 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


Report •

#3
December 30, 2019 at 10:57:20
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

http://www.skeptic.com/


Report •

Related Solutions

#4
December 30, 2019 at 11:09:01
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


Report •

#5
December 30, 2019 at 11:39:14
My guess is that this question is related to his previous thread

Kinda 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

http://www.skeptic.com/


Report •

#6
December 30, 2019 at 20:56:12
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.

Report •

#7
December 30, 2019 at 23:39:06
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


Report •

Ask Question