I am trying to figure out the formula for how to extract a segment of text from a string with a fixed starting position but variable length, and variable starting position with fixed length. I feel like I should use a mid formula but I am not sure what formula to use for the variable length. Below is my example:
400000 005-038 10 9999400 1
400000 007-707 10 107412 1
The first set of numbers I need to extract are the ones highlighted in yellow. The start position will be the same but the length will be different.
The second set of numbers I need to extract are the ones highlighted in red. The start position is variable but the length will be fixed.
I want these values in 2 separate fields in my table.
Any help would be appreciated. I am trying to setup a template for individuals that don't know much about Excel and would be challenged by using text to columns.
I am using MS Access 2013.
Thanks,
Wendy