Hello, I am working on a spreadsheet with over 140,000 rows that has a column of numbers that range from 1 to 6 digits. I need to extract the first number from the right, second and third numbers from the right, and fourth to sixth numbers from the right. I have had issues due to the number I need to extract from can vary from 1 to 6 digits. The following is what I am trying to achieve:
I then need to convert these numbers to total seconds. I need to multiple a number in the 4th to 6th digit by 60, then add seconds in 2nd and 3rd digit, and add a 1 or 0 from the first digit. The following is an example for the top example of 103308: (103*60)+(30)+(1) = 6,211 seconds.
Thank you for your help.
| Example Number String Format | Only 4th to 6th numbers to right (minutes) | Only 2nd and 3rd number from right (seconds) | Only 1st number from right (1/10 seconds) |
103308 | 103 | 30 | 8 |
10280 | 10 | 28 | 0 |
8423 | 8 | 42 | 3 |
534 | 0 | 53 | 4 |
12 | 0 | 1 | 2 |
6 | 0 | 0 | 6 |
| Note: if I get a 1 to 9 in this column it needs to = 1, otherwise stays as a 0. |
Thank you for your help.
