Find numeric value within a string based on a particular set of formats

[lstolte91]

Hello,

I'm trying to parse out certain numbers from a string field based on the format of those numbers. For example, I know the numbers will be in formats such as:

• ###.#
• ###.##
• ####.##
• ####.#
• ########.#
• ########.##
• etc...

There are always between 3-8 pre-decimal digits, and between 0-2 post-decimal digits, but any iteration within those parameters is possible.

Is there a way to search for or parse out information from a string field based on the "format" of the information I'm looking for? Ideally, I'd like to somehow tell Alteryx that the data needs to match one of the above formats and to extract that data to a new field if it fits any of those formats. This seems like something that the Regex tool/formulas might be able to help with, but I'm relatively new to Alteryx so I don't have much experience with those tools.

The dataset is very large (1.9M records in the example I'm using, but can be more) so anything that might add a significant amount of time to process isn't ideal, but can be worked with if needed.

Any suggestions are welcome and appreciated!

[aish49]

Hi !

Can you elaborate the problem more ?

[jdunkerley79 · Accepted solution]

A formula like:

ToNumber(REGEX_Replace([Input],"^.*?(\d{3,8}(.\d{1,2})?).*?$","$1"))

should do what you want.

Examining the REGEX in more details:

• ^.*? matches anything from start of string non-greedily
• \d{3,8} matches the first part of the number, 3 to 8 digits
• (.\d{1,2})? optionally matches the decimal part of the number
• .*?$ matches the rest of the string

As the 2 and 3 parts are in brackets the number will be stored in $1

Sample attached

[lstolte91]

@jdunkerley79, that's exactly what I was looking for, thank you! We added a [.] to capture the decimal where applicable, but otherwise were able to just put your tool right into our workflow.