Strip multi character string into numbers and paste to respective columns

[Shaolin666]

Hi,

I have a column with various strings in it representing time periods:

1w10h
19h36m
15h49m
22h36m
2mo2w
5mo1w
6d13h

where:

y = years
mo =months
w = weeks
d = days
h = hours
m = minutes

How would I go about stripping each string into columns such that it shows the following:

STRING	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINUTES
1w10h	0	0	1	0	10	0
19h36m	0	0	0	0	19	36
15h49m	0	0	0	0	15	49
22h36m	0	0	0	0	22	36
2mo2w	0	2	2	0	0	0
5mo1w	0	5	1	0	0	0
6d13h	0	0	6	13	0	0

Any help appreciated.

Kind regards

Shaolin666

[István Hirsch]

Enter the bold abbreviations into row 1, then enter into B2 (supposing minutes/m/ -if exists – is always at the end):

=IFERROR(LOOKUP(1000,--MID($A2,SEARCH(B$1,$A2)-{1,2,3},{1,2,3})),0)

Copy down and across as needed.

[István Hirsch]

From B1 to G1, enter: | Y | Mo | W |D |H | Min |

[tim201110]

smth like that

[István Hirsch]

smth like that

I do not get correct result if both "mo" and "m" also exist in the string. I guess m for minutes is found in mo for months, this causes the problem.

[tim201110]

yes, maybe Shaolin666 will do smth with it

[Shaolin666]

Thank you very much for your help and suggestions. it doesn't matter that "mo" and "m" together give an error since they never appear together in the same string.

thank you very much for all of your help. i used your examples and have it working as desired

kind regards

Shaolin666

[István Hirsch]

Thank you very much for your help and suggestions. it doesn't matter that "mo" and "m" together give an error since they never appear together in the same string.

thank you very much for all of your help. i used your examples and have it working as desired

kind regards

Shaolin666

Edit: my formula (post #2) correctly:

=IFERROR(LOOKUP(1000,--MID($A8,SEARCH(B$1,$A8&"in")-{1,2,3},{1,2,3})),0)


I must be doing something wrong, but for me the formula in post #4 does not give the needed results for example, for strings like 10w2h, 19h3m, 2h3m, 2mo1w, 9d6h etc. Do you get the correct result with these examples?

[Indi_Ra]

it doesn't matter that "mo" and "m" together give an error since they never appear together in the same string.

in this case:
2mo2w 0 2 0 0 0 2 with month and weeks, tim201110 formula is wrong.

[Shaolin666]

Thank you István Hirsch, you are correct!!

In my hurry to leave the house yesterday it looked correct, but your correction is nearly 100% correct.

Using your amended formula:

=IFERROR(LOOKUP(1000,--MID($A8,SEARCH(B$1,$A8&"in")-{1,2,3},{1,2,3})),0)

There is a small error with "mo" entries

i.e.

2mo2w & 5mo1w gives:

STRING	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINUTES
2mo2w	0	2	2	0	0	2
5mo1w	0	5	1	0	0	5

0 2 2 0 0 2
0 5 1 0 0 5

This forumla is adding minutes when there are none. Taking the same value for months

[Indi_Ra]

Using István Hirsch formula:

=IFERROR(LOOKUP(1000,--MID($A2,SEARCH(B$1,$A2&"in")-{1,2,3},{1,2,3})),0)

you get:

STRING Y Mo W D H Min
1w10h 0 0 1 0 10 0
19h36m 0 0 0 0 19 36
15h49m 0 0 0 0 15 49
22h36m 0 0 0 0 22 36
2mo2w 0 2 2 0 0 0
5mo1w 0 5 1 0 0 0
6d13h 0 0 0 6 13 0

and this is correct. No minutes as you specify. Maybe you dont change formula for each places...

[István Hirsch]

Please use the abbreviations suggested and check if the value of cell G1 is "Min" and not only "M":


My version.jpg

[Shaolin666]

Please use the abbreviations suggested and check if the value of cell G1 is "Min" and not only "M":


Attachment 364963

Excellent, Thank you!

I now have it working perfectly thanks to your advice of using the abbreviations as suggested using "Min" instead of "m".

Sincerely, thank you very much!! I appreciate it

kindest regards

Shaolin666

[István Hirsch]

You are welcome, thanks for the feedback.

[Shaolin666]

You are welcome, thanks for the feedback.

Thank you once again....I added to your reputation for your help with my problem. I also added to Indi_Ra's rep.

You are an excel star