vladimiratanasiu
Active Member
- Joined
- Dec 17, 2010
- Messages
- 347
- Office Version
- 365
- 2021
- Platform
- Windows
Hello!
I have a table with cells including alphanumerical strings, hours and text formats. I need a formula to extract hours, from all cells where they are present. I found one solution (see attached table) that runs ok only if the type of content is alphanumerical, but it fails when it's formatted as time/hour only. Do you know any formula to do this task, meeting both the time and the alphanumerical formats and ignoring the text-only strings? Thank you!
I have a table with cells including alphanumerical strings, hours and text formats. I need a formula to extract hours, from all cells where they are present. I found one solution (see attached table) that runs ok only if the type of content is alphanumerical, but it fails when it's formatted as time/hour only. Do you know any formula to do this task, meeting both the time and the alphanumerical formats and ignoring the text-only strings? Thank you!
| Book2.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 12 | 1 | 08:04 | |||
| 13 | 2 | OR | |||
| 14 | 3 | 00:00 | |||
| 15 | 4 | OR | |||
| 16 | 5 | 00:00 | |||
| 17 | 6 | 08:00 | |||
| 18 | 7 | ABCD 7:01 1111 | 7:01 | ||
| 19 | 8 | EFGH 11:50 2222 | 11:50 | ||
| 20 | 9 | IJKL 14:00 334 | 14:00 | ||
Sheet1 | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C12:C20 | C12 | =IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(B12,")",""),".",""),FIND(":",B12)-2,FIND(":",REPLACE(B12,99,FIND(":",B12),""))-1)),"") |
