Forum Discussion
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 return a 4) and lastly if all 0000 just return a blank cell
Examples:
1. 7000000001003313150000000000010000000000934000000000
2. 7000000001003313150000000000010000000000004000000000
3. 7000000001003313150000000000010000000000000000000000
Results I would like is
1. 934
2. 4
3. Blank cell
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!
10 Replies
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!
That could be
=SUBSTITUTE(MID(A1,41,4), "0", "" )Please note, we return texts, not nubmers
=LET( a,--MID(A1,40,4), b,IF(a=0,"",a), b)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.
