CODEWITHSUNDEEP
excelstringnumbers
CRE123
CRE123

Reputation: 15

how to split time into days hours minutes seconds from a text string

I've got a string of time appearing as text that I need to convert to numbers. Here is an example:

1

How do I convert the string into separate columns. One column for days, one for hours, etc.?

Upvotes: 0

Views: 216

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

put the durations as titles and use:

=FILTERXML("<t><s>" & SUBSTITUTE(LOWER($A2)," ","</s><s>") & "</s></t>","//s[starts-with(.,'"&LOWER(B$1)&"')]/preceding-sibling::*[1]")

Put that in B2 and copy over.

enter image description here

Upvotes: 1

Related Questions