Extracting text from a string

[Whughes98]

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

[jwhite]

Don't think there is an answer for this one because the starting position can be different AND the variable length can be different from record to record. How are the users entering the data. In Excel? Why not split the "yellow" data and "red" data into separate columns?

[June7]

If red data is always 3 characters, try:

Red: Right([fieldname], 3)

Yellow: Left([fieldname], Len([fieldname])-3)


As already suggested by jwhite, would be better to enter into separate columns to begin with.

[Whughes98]

That is what I am trying to do. Sorry that wasn't clear.

[June7]

You are trying to fix existing data and future entries will be into separate fields? Good. Did post 3 resolve issue? Do you need to run an UPDATE query to fix data in Access table?

[davegri]

Couple of UDFs can do it.

Function GetRed(arg As String) As String
    Dim rslt As String
    rslt = Left(arg, Len(arg) - 2)
    GetRed = Right(rslt, 3)
End Function


Function getYellow(arg As String) As String
    Dim yStart As Long
    Dim yEnd As Variant
    yStart = 19
    yEnd = Len(arg) - 4 - yStart
    getYellow = Mid(arg, yStart, yEnd)
End Function