Excel 2007 : Extracting alphanumeric strings

Hello

First post around here

I have to write monthly reports and the software from the data come does not export. I had to copy and paste all the values.

To make some statistics I have to convert time values furst. Values after paste comes as 7h38m2s, 7 hours, 38 minutes, and 2 second.

There is problem to extract the number using Extract function because values does not always comes in same order. Some examples I have to deal with

1d8h0m
7d7h22m
20d17h22m
15h57m16s
2h37m20s
14h7m5s

So on.There no standard dd,hh,mm,ss
As you can see, there are many values and I have to deal with all. Zero is always ignored and this is the problem to extract function. Because I have to predefine the range of the row to extract but positions changes, and row range sometimes does not work.

I folloed this http://office.microsoft.com/en-us/ex...001154901.aspx

How to I extract that values into three cells at least. Hour cell, Minute cell and Second cell.

Do you know some macro that can help me in conversion?

Thanks in advance.

one way with formulas

Some more formula suggestions

Table1 extracts days and time using helpers

Table2 extracts days and time no helpers

Table3 extracts days, hours, minutes, and seconds

I don't know how Column C is derived, but it appears it can be used as a helper column.
In D2

=IF(C2="0s","- no reply -",IF(ISERROR(SEARCH("h",C2)),"0h"&C2,C2))

This adds the missing hour value, and defines "- no reply -", then we can add to the formulae in G2:H2 to handle this new list.

Another approach (see Sheet2) might be to use Column D to unify the data in Column C to a common "_d_h_m_s" format, then the formulae in F2:I2 become much simpler.
In D2 (This could be simplified for 2007 and above using IFERROR())

=IF(ISERROR(SEARCH("s",IF(ISERROR(SEARCH("d",C2)),"0d"&IF(ISERROR(SEARCH("h",C2)),"0h"&IF(ISERROR(SEARCH("m",C2)),"0m"&C2,C2),C2),C2))),IF(ISERROR(SEARCH("d",C2)),"0d"&IF(ISERROR(SEARCH("h",C2)),"0h"&IF(ISERROR(SEARCH("m",C2)),"0m"&C2,C2),C2),C2)&"0s",IF(ISERROR(SEARCH("d",C2)),"0d"&IF(ISERROR(SEARCH("h",C2)),"0h"&IF(ISERROR(SEARCH("m",C2)),"0m"&C2,C2),C2),C2))

In both examples I have added conditional formatting to highlight "no replies"