# Excel - COLUMN Function Problem - Expert Solution

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
Excelchat Expert 26/08/2018 - 01:19
Excelchat Expert 26/08/2018 - 01:19
Thank you.
Excelchat Expert 26/08/2018 - 01:20
User 26/08/2018 - 01:20
Excelchat Expert 26/08/2018 - 01:20
Excelchat Expert 26/08/2018 - 01:20
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
.
Excelchat Expert 26/08/2018 - 01:28
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.