Forum Discussion
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 FunctionSimply 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!
FatManFluff Just to clarify, what is the expected result if the 4-digit string is 0205 or 2050? I assumed you wanted both to return 205, not 25 or 2050. Likewise, 7006 would return 7006, not 76, correct? Or how about 1000? Should it return 1000 or 1?
- Sorry trying to go through all the options provided from everyone just having a hard time implementing into my system. The substitute was the easiest to follow but like mentioned above ran into the issue with it removing all 0's (when 205 is the result it is showing 25). My text string I am taking mid from is a lot more complex than 30 characters I trimmed it down for example purposes but im looking at a text string of 350 characters with letters and numbers
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 FunctionSimply 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!
Good morning, So I noticed this morning that I was having an issue with the formula NOT returning a 0 when it ends with it. For example in the attached file if I change the 934 to 930 is just shows 93. Is there something else that needs to be changed with the formula so it can return the 0 at the end? (it also happens when the value is 10 shows 1, 100 shows 1, 1000 shows 1) Just like your 5 row on the attachment I would like it to show 1000 instead of 1.
