how to extract numbers from excel cell in the form of "x

The cell is in the format of "6:00 - 14:00"; showing hours worked in 24 hour format. I would like a way to convert that in a way so I can perform operations with them.

I'm thinking if I can extract the "6:00" to a single cell with time format, and the same with the "14:00". Maybe I can use =RIGHT()/LEFT() but the number of characters would change each time as it is 24-hours format.

Any thoughts? \

CodePudding user response：

Assuming the value is in cell A1, to find the 6:00 ...

=TRIM(LEFT(A1,FIND("-",A1) - 1))

... and to find the 14:00...

=TRIM(MID(A1,FIND("-",A1)   1,100))

CodePudding user response：

FILTERXML() would be a good choice in this case.

=FILTERXML("<t><s>"&SUBSTITUTE($A$1,"-","</s><s>")&"</s></t>","//s[1]")

Above formula will extract first part of time and will return as time value. If you need to format it as time then use TEXT() function like-

=TEXT(FILTERXML("<t><s>"&SUBSTITUTE($A$1,"-","</s><s>")&"</s></t>","//s[1]"),"hh:mm:ss")

To extract second part just change [1] to [2] like-

=TEXT(FILTERXML("<t><s>"&SUBSTITUTE($A$1,"-","</s><s>")&"</s></t>","//s[2]"),"hh:mm:ss")

CodePudding user response：

Even with a 24-hours notation, you could still extract the 1st 5 and last 5 characters with LEFT() and RIGHT() since you use a leading and trailing space with the hyphen:

Formula in B1:

=--LEFT(A1,5)

Formula in C1:

=--RIGHT(A1,5)