MID Function Help

FatManFluff Please see the attached workbook, which contains a few different options. I'm still not sure what version of Excel you're using. The first two examples should work in any version as far back as Excel 2010. The third example uses LET, so will only work with Excel 2021 or newer. The last two examples use LAMBDA recursion, so will only work with Excel for MS365 or Excel for the web.

The second example uses a relative named formula, defined in Name Manger, and the last example uses a custom function, also defined in Name Manager (Ctrl+F3).

The first three examples will only work if the targeted 4-digit code is numeric, whereas the last two examples will also work if the 4-digit code contains alphanumeric characters.

If you need an alphanumeric option that's compatible with older versions of Excel, try the following custom VBA function, which is similar to Hans Vogelaar's solution on the linked thread in my first reply:

Function LRTrimChar(text As String, char As String) As String
    Dim str As String: str = text
    Do Until Left(str, 1) <> char
        str = Right(str, Len(str) - 1)
    Loop
    Do Until Right(str, 1) <> char
        str = Left(str, Len(str) - 1)
    Loop
    LRTrimChar = str
End Function

Simply open the Visual Basic Editor (Alt+F11), go to Insert > Module and paste the code into the new module. Then you can use the LRTrimChar function just like any other function in your workbook. For example:

=LRTrimChar(MID(A2, 40, 4), 0)

Note: if you go with the custom VBA function, you will need to save the workbook as a Macro-Enabled Workbook (*.xlsm).

Hopefully at least one of these suggestions can be adapted to meet your needs. Cheers!