EXTRACT TIME FROM TEXT

Question

Hello everyone,

I am having a problem here and I would really appreciate your help.

I want to extract a certain value from a text , which is time (00:00).

E.x. Tom goes to school at 07:00 o'clock

e.x.2  07:00 is the time that Tom goes to school

I give two examples because the time value is not situated in the same place at all rows. The value I want to isolate is 07:00 in this example.

How can I extract only the time info in a side column(or even same) column???

Thank you!!!

Answer 1

Hi,

Use below formula:

=VALUE(LEFT(RIGHT(A2,LEN(A2)-MIN(FIND({1,2,3,4,5,6,7,8,9,0},LOWER(A2)&1/17))+1), AGGREGATE(15,6,FIND(LOWER(CHAR(ROW(65:90))),RIGHT(A2,LEN(A2)-MIN(FIND({1,2,3,4,5,6,7,8,9,0},LOWER(A2)&1/17))+1)),1)-2))

Format column B as time hh:mm

Hope this helps.

Regards,

IlirU

Answer 2

Hi,

Given the data showing the screenshot below, in range B2:B3, I typed '07:00 and '06:30.  I select B2:B4 and went to Data > Flash Fill.

Hope this helps.

Answer 3

Hi aggelikikos,

Have you referred to the reply provided by Advisor above? Please let us know if you still need any further help.

Regards,

Waqas Muhammad

Answer 4

Yes Rohn007, you have right. Maybe it is better to use MID function. In this case I created this formula:

=TIME(MID(SUBSTITUTE(TRIM(A2)," ",""),FIND(":",SUBSTITUTE(TRIM(A2)," ",""))-2,2), MID(SUBSTITUTE(TRIM(A2)," ",""),FIND(":",SUBSTITUTE(TRIM(A2)," ",""))+1,2),)

This formula ignore spaces made by rush or mistake (see the image above). For sure, cells in column B must be formatted as time (hh:mm).

Thanks and regards,

IlirU

Answer 5

I used the Mid() function

=TRIM(MID(A1,(SEARCH(":",$A$1)-2),2)&MID(A1,(SEARCH(":",$A$1)),3))