Previous Topic Next Topic

Seperating time from text

Solved/Closed
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020 - Nov 23, 2019 at 02:24 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Nov 25, 2019 at 11:42 AM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Nov 25, 2019 at 11:42 AM
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