**Question description:**

*This user has given permission to use the problem statement for this blog.*

Each line following is a separate cell in a column.
Is there any way to extract the times (left numbers only) from these cells to calculate elapsed time.
11:59p-8a
9a-5p Mt/Sup
"4p-12a"
9a-5p Mt/Stk 1-8
"2p-10p"
9a-5p Mt/Escort 7-10
6a-5p A2/Stk Op 6-8
9a-5p Trn/Stk 5-2

Solved by E. H. in 29 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
26/08/2018 - 01:00

Hi

Excelchat Expert
26/08/2018 - 01:01

Welcome to Got IT Pro.

Excelchat Expert
26/08/2018 - 01:01

Please share the sample in the preview.

Excelchat Expert
26/08/2018 - 01:01

I would like to see what information from left you are looking for. It is difficult to read the text in the description.

Excelchat Expert
26/08/2018 - 01:03

Copy it from your local excel file to here.

Excelchat Expert
26/08/2018 - 01:05

What is the desired format in the column B?

User
26/08/2018 - 01:05

I wish to be able to calculate the elapsed time

User
26/08/2018 - 01:06

Col A only, can i extract the times to do this?

User
26/08/2018 - 01:07

Data is entered sometimes with quotes, and never with am or pm, on a or p.

Excelchat Expert
26/08/2018 - 01:07

I am sorry, my net was disconnected for a moment.

User
26/08/2018 - 01:08

I've tried using left, find (for the -) to find the times, but can be hour, or hour and min entries.

Excelchat Expert
26/08/2018 - 01:08

ok. a or p is used for am or pm?

User
26/08/2018 - 01:08

yes.

Excelchat Expert
26/08/2018 - 01:08

I am trying in the preview.

Excelchat Expert
26/08/2018 - 01:15

=timevalue(substitute(substitute(LEFT(B1,find("-",B1,1)-1),"a","AM"),"p","PM"))

Excelchat Expert
26/08/2018 - 01:15

It is in column C

Excelchat Expert
26/08/2018 - 01:15

=substitute(if(iferror(find(" ",A1,1),0)=0,A1,mid(A1,1,find(" ",A1,1))),"""","")

Excelchat Expert
26/08/2018 - 01:15

It is in column B.

Excelchat Expert
26/08/2018 - 01:15

Same way, you can get the right side time also.

Excelchat Expert
26/08/2018 - 01:17

=timevalue(substitute(substitute(RIGHT(B1,len(B1)-find("-",B1,1)),"a","AM"),"p","PM"))

Excelchat Expert
26/08/2018 - 01:17

It is for column D.

Excelchat Expert
26/08/2018 - 01:17

Hope, this is what you are looking for?

User
26/08/2018 - 01:18

awesome, how do i keep this to reference?

Excelchat Expert
26/08/2018 - 01:18

What reference?

Excelchat Expert
26/08/2018 - 01:18

I prefer to keep this in 3 stages. one to extract the column B and then times in next columns.

User
26/08/2018 - 01:19

sounds good. can I get a copy of the blank.xlsx so i can study it?

Excelchat Expert
26/08/2018 - 01:19

Please download the file.

Excelchat Expert
26/08/2018 - 01:19

File / Download as Excel.

Excelchat Expert
26/08/2018 - 01:19

Thank you.

Excelchat Expert
26/08/2018 - 01:20

Have wonderful day ahead.

User
26/08/2018 - 01:20

where do i download?

Excelchat Expert
26/08/2018 - 01:20

File menu.

Excelchat Expert
26/08/2018 - 01:20

Download as excel. It can be downloaded from preview.

User
26/08/2018 - 01:22

found it. thank you. perfect solution.

Excelchat Expert
26/08/2018 - 01:22

you are welcome.

Excelchat Expert
26/08/2018 - 01:22

Please end the session and leave a valuable feedback.

Excelchat Expert
26/08/2018 - 01:22

please do visit Got IT Pro.

User
26/08/2018 - 01:22

one last thing, format for col c & D? i get value error

Excelchat Expert
26/08/2018 - 01:23

I have formatted using hh:mm:ss AM/PM

Excelchat Expert
26/08/2018 - 01:24

Please remove timvalue in excel file.

Excelchat Expert
26/08/2018 - 01:25

It is not working. In Google sheets, it works.

User
26/08/2018 - 01:25

I see. I still have #value! error, hmmmm. Cols a & b are general

Excelchat Expert
26/08/2018 - 01:26

Ok. I am checking in excel. Please give me few minutes.

User
26/08/2018 - 01:27

no prob

Excelchat Expert
26/08/2018 - 01:27

.

[Uploaded an Excel file]

Excelchat Expert
26/08/2018 - 01:28

Please download file from here.

User
26/08/2018 - 01:29

Got it! And all seems good. I think I can handle it from here. Very good feedback coming...

Excelchat Expert
26/08/2018 - 01:29

Thank you.

Excelchat Expert
26/08/2018 - 01:29

Great to hear it.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*