ToNumber(TRIM) Formula with Resulting Conversion Error Reached

[TR]

I have a file name I'm wanting to parse only the numbers from. Here are 3 examples of the filename in the format I'm working with: (Store107BGs_2015.yxdb, Store7BGs_2015.yxdb and Store1007BGs_2015.yxdb). The successful parse result would be 107, 7 and 1007.

I'm using a Formula with the following expression: ToNumber(Trim([FileName],"Store")) and I'm getting successful results but with Conv Errors: the Formula (14) TONUMBER: 107BGs_2015.yxdb lost information in translation,  Formula (14) TONUMBER: Conversion Error Limit Reached.

Although I'm getting the correct results, what is a cleaner way to do this?

Thanks!

[michael_treadwell]

The problem is that the TRIM function is returning 107BGs_2015.yxdb which you are then trying to convert to a number via TONUMBER()

Trim 'Store' from the left and then 'BGs_2015.yxdb' from the right and then convert to a number. I've done this and used the Select tool to convert.

The most robust way to do this would be to use REGEX_REPLACE() to replace the 'BGs_2015.yxdb' so that your function doesn't stop working when the year changes.

Probably something like this: REGEX_REPLACE([Field1], 'BGs_\d{4}.yxdb', '')

[GarthM · Accepted solution]

how about this

ToNumber(
  REGEX_Replace([FileName], '.*?(\d+).*', '$1')
)

this assumes you're creating a separate field with a numeric type. 

[TR]

Thanks for the reply @michael_treadwell! Your sample worked fine, however when integrated back into my workflow it gave me the same results I had stumbled upon before with incomplete parsing. I was left with multiple 'Store' results and some with 'Store107', 'Store108'... The REGEX was a super recommendation and the follow-up suggestion looks to have used it and will be the one that may work well.

[TR]

Works perfectly! Still wrapping my head around the REGEX and it's capabilities, thanks.

[GarthM]

maybe this will help:

Regex Crossword

[TR]

[Trying hard not to get sidetracked by playing Regex Crossword]

Thanks, that's great!

[GarthM]

ha! good luck with staying on task. regex crosswords are addicting.