extract time from text and condition

[tonyrensya]

Hello all,
I'm facing a problem in extracting time from raw data, which the reference and expected result are as below.

If slot is a text, retrieve start time and end time in Result A and B

Slot	Result A	Result B
09:00-15:00	09:00	15:00
A	08:00	12:00
P	18:00	24:00

if Slot is a letter, reference to the table below and output start time and end time in Result A and B

	Start time	End time
A	09:00	12:00
P	18:00	24:00

Thank you very much!

 

[Legacy 143009]

Maybe something like this:
Result A:

=IF(LEN(A1)>1,LEFT(A1,5),LOOKUP(A1,{"A";"P"},{"09:00";"18:00"}))

Result B:

=IF(LEN(A1)>1,RIGHT(A1,5),LOOKUP(A1,{"A";"P"},{"12:00";"24:00"}))

 

[tonyrensya]

Maybe something like this:
Result A:

=IF(LEN(A1)>1,LEFT(A1,5),LOOKUP(A1,{"A";"P"},{"09:00";"18:00"}))

Result B:

=IF(LEN(A1)>1,RIGHT(A1,5),LOOKUP(A1,{"A";"P"},{"12:00";"24:00"}))

Dear Flashbond,

Thank you for your reply, as I have so many conditions in the letter table, I hope the formula wont be too long and complex, are there any simplify ways?

 

[Legacy 143009]

Then you have to make a helper table. Like:

Column D	Column E	Column F
A	09:00	12:00
B	12:00	18:00
C	18:00	24:00

Then your new formulas will look like:

=IF(LEN(A1)>1,LEFT(A1,5),VLOOKUP(A1,$D$1:$F$3,2,0))
=IF(LEN(A1)>1,RIGHT(A1,5),VLOOKUP(A1,$D$1:$F$3,3,0))

 

[Phuoc]

Try this:

Book1
	A	B	C
1	Slot	Result A	Result B
2	09:00-15:00	9:00	15:00
3	A	9:00	12:00
4	P	18:00	24:00
5
6	if Slot is a letter, reference to the table below and output start time and end time in Result A and B
7		Start time	End time
8	A	9:00	12:00
9	P	18:00	24:00:00
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(VLOOKUP(A2,$A$8:$C$9,2,0),LEFT(A2,5)+0)
C2:C4	C2	=IFERROR(VLOOKUP(A2,$A$8:$C$9,3,0),RIGHT(A2,5)+0)