Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Seperating time from text

[Solved/Closed]
Report
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020
 -
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
 -
Hello,

I have a task in my office that i can't get done and it is very impossible to finish within 1 week as these are 14,000+ orders. The sheet looks like this.



The underline time are the pick up time which I have to extract and put on the last row of the sheet that is under "pick up time" Please tell me how i can extract this from the cell because it is impossible to do everything manually.

1 reply

Answer 1 / 1
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
 468
Hi Luigi,

The formula below will extract the last 5 character of the text string of column H. The extracted string is changed into time format, so you can apply any time format you want. When the extracted string is not a time, then the destination cell is left blank.

Here is the formula:
=IF(ISERROR(TIME(LEFT(RIGHT(H2,5),2),RIGHT(RIGHT(H2,5),2),)),"",TIME(LEFT(RIGHT(H2,5),2),RIGHT(RIGHT(H2,5),2),))

Best regards,
Trowa
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!