Forum Discussion

FatManFluff's avatar
FatManFluff
Brass Contributor
Aug 07, 2024
Solved

MID Function Help

Hello I am trying to use MID function for returning a 4 digit number from a text string but it isnt always 4 digits. How do I avoid the return result to be 9340 (when its actually 934) or 0004 ( just...
Formulas and Functions
  • djclements's avatar
    djclements
    Aug 07, 2024

    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!

djclements's avatar
djclements
Silver Contributor
Aug 07, 2024

FatManFluff 

 

You might find some of the suggestions on the following thread useful:

 

In the context of the example strings provided (where the first 30 characters are excluded), you could use LAMBDA recursion to remove all leading and trailing zeros from the last 22 characters of each string:

 

=LET(
    F, LAMBDA(X,s,c,
        LET(
           a, IF(LEFT(s)=c, RIGHT(s, LEN(s)-1), s),
           b, IF(RIGHT(a)=c, LEFT(a, LEN(a)-1), a),
           IF(OR(LEFT(b)=c, RIGHT(b)=c), X(X, b, c), b)
        )
    ),
    F(F, RIGHT(A1:A3, LEN(A1:A3)-30), "0")
)

 

Recursive LAMBDA Example

 

Please note, this method requires Excel for MS365 or Excel for the web.

Resources