expression to capture varying length of numbers between brackets

[Paintballlovr]

I'm trying to figure out how to capture a number that appears between [ ] in a varying length field. I'm currently using:

Financial Class code: Mid([tbltemp_PB_import]![payor financial class],InStr([tbltemp_PB_import]![payor financial class],"[")+1,4) , which is giving me everything after [. Instead of going over 4 characters though, I want to stop at ], so I only display the number within. The field I'm trying to capture this number from is varying length, as well as the number within the [ ] can be a varying length. Here is a sample of the data I'm trying to break up. Any ideas?

Payor Financial Class
Nebraska Medicaid [104]
Medicare [2]
MEDICARE ADVANTAGE [101]
Nebraska Medicaid [104]
Medicare [2]
Blue Cross Blue Shield [100]

[pbaldy]

If it's always at the end:

?Val("123]")
123

[lfpm062010]

Try,

Financial Class code: Mid([tbltemp_PB_import]![payor financial class],InStr([tbltemp_PB_import]![payor financial class],"[")+1, InStr([tbltemp_PB_import]![payor financial class], "]") - InStr([tbltemp_PB_import]![payor financial class], "[") - 1)

[Paintballlovr]

ifpm062010's answer is what I had pictured in my head, but both solutions appeared to achieve the same result, so thank you to both. I will mark this thread as solved, but can I ask a theory question based on these two solutions? Are there any implications of using one of the solutions versus the other? Thinking down the road, will this new field be formatted the same either way? When I tried both solutions, I noticed that pbaldy's solution pushed the numbers to the right side of the field, whereas ifpm062010's solution left the numbers on the left. Maybe it doesn't matter, I just want to understand the function behind the expression, so I can use it properly down the road. Thanks again!!

[pbaldy]

From help, the Val() function "stops reading the string at the first character it can't recognize as part of a number" Since your existing function would return a value that starts with a number, the Val() function is a shortcut that will trim off the non-numeric portion and as you saw, returns a numeric value. The other function is longer but should return the same numbers. As you also saw, because the field it started with was text, it returned a text value. That could be overcome by adding a conversion function like CInt(). As is often the case, more than one way to skin a cat.

[Paintballlovr]

From help, the Val() function "stops reading the string at the first character it can't recognize as part of a number" Since your existing function would return a value that starts with a number, the Val() function is a shortcut that will trim off the non-numeric portion and as you saw, returns a numeric value. The other function is longer but should return the same numbers. As you also saw, because the field it started with was text, it returned a text value. That could be overcome by adding a conversion function like CInt(). As is often the case, more than one way to skin a cat.

Thank you very much for the detailed explanation!

[pbaldy]

Happy to help!