Numeric Wildcard in Formula Tool

[MemphLantAntonio]

I do not use Regex often, but I am looking for a wildcard for any numeric character.

I have a long note field that contains keys for customers but these keys contain commas that need to be removed. The problem is, in order to parse it, I want to replace the comma with a space when it is a comma being used to separate distinct values of the customer number but replace the comma with nothing (remove comma) when it is within the key.

For example:

4,300,4,500,4,600 is a list of 3 customer numbers. In this case, I would want to replace the comma in 4,300 with nothing so it is 4300 but I would want to replace the comma between4,300 and 4,500 with a space so I can text to rows it and split them out into separate values.

he rules would be different for #,### as ###,#

[BS_THE_ANALYST]

@MemphLantAntonio is this what you're looking for:

Before:

After:

[JVMORO]

Hello, if your field is fixed, always 12 values, this can be a solution, if you have more, just add it in the formula tool

[MemphLantAntonio]

I think these 2 are OK, I don't quite understand how the TileSequenceNum is working. I should have clarified that the note field will have high variability, and I am not sure either of these 2 can account for that. Here are more examples:

Note                                                         Desired text to column 1               Desired text to column 2               Desired text to column 3

4,500,6,500,7,500 are all eligible                           4500                           6500                                7500

Please add 4,500, 6,500, and 7,500 to contract X.4500                          6500                                7500

#45,500 needs to be added to contract X               45000   

I would just use the Data Cleansing to remove punctuation, but in example 1 it would make the 3 numbers combine into one number, and if I use space as delimeter for text to columns, it would only parse into 1 field rather than 3 separate numbers.

[Yoshiro_Fujimori · Accepted solution]

Hi @MemphLantAntonio ,

I made a workflow which returns the same value as your sample data.

You may need to adjust the workflow with the maximum number of values in the string.

Regex: \d*,?\d{3}

I hope this works.

[Yoshiro_Fujimori]

Please note the RE below may not work if the number can be less than 1000...

Please adjust it as necessary.

[MemphLantAntonio]

Thank you this is what I was looking for!