hi! I have a field that is manually entered and I’m trying to create a formula field that extracts the middle number (found between “-” and “.”) but excludes the starting zero’s. This middle number can range from 1 to 999. I’ve tried a variety of Right/Left & MID, but I can’t seem to exclude the starting zero’s.
It looks like you have a typo in your example. Should the middle example be 25 instead of 26? If this isn’t a typo, this is a much more complex situation.
You may want to escape the . in your expression. A period in a RegEx can be interpreted as any character and not necessarily a literal period. In this case, it probably won’t make a difference since the expression is greedy, but it is still good practice.
This is a bit dangerous because it will remove all zeros, not just leading zeros. It also will return only the digits before any internal or trailing zeros, because REGEX_EXTRACT only returns the first match, not all matches. If the number haves internal or trailing zeros, you will get the wrong result. For example “123456-010.5” should return 10, and not 1. This is why it is important to have robust test cases.
It also isn’t clear if @Shawntel_Cote wants the final result to be a text string that looks like a number, or an actual number that math can be performed on.