Regex Question

[SouravKayal]

Hi Guys, 

I have a string which reads like "1122 | xyx > abc ; 2212 |" 

What i Want to do is count the numbers : Ex for above the count should be 2 ( for 1122 and 2212)

Or 

Split the data to columns with one having 1122 and another 2212. 

I have been trying to use regex but  unable to hit the right expression.

[marcusblackhill]

Hey @SouravKayal !

To count how many number you have, can use the expression REGEX_CountMatches([Field1], "\d+") in formula tool for that.

Hope that helps

[marcusblackhill]

Ah, and if you want to split, can do like in the print below:

[SouravKayal]

THe below solution looks good but if i do not know the range of data , for example as you have specified 3. is there a way i can leave the range to be auto ?

[marcusblackhill · Accepted solution]

You can do a workaround for that. Configure to split in rows instead of columns and add a cross tab after to create a column for each row.

[SouravKayal]

is there a direct formula i can use as i know the expression will begin with the number and end with space and | and the next number will begin with ; and then space and the number then space and |

ex: 1234 | xyx?abv? ;5567 | abcde> ;4434 |

[marcusblackhill]

@SouravKayal 

I think not, but look that example workflow, that is a workaround I talked about to automate the selection. Put to split in columns with a high number of columns, I used 50 but can be more if you think you need, after that the transpose/crosstab logic will remove the null columns and fix only the columns with data.

Hope that help!

[HomesickSurfer]

Hi @SouravKayal 

Please review the below solution.

Split your string to rows using the Text to Column tool, then; remove letters, punctuation and whitespace using the below formulas.

[estherb47]

Hi @SouravKayal 

Here is a solution that finds both the number of numbers, and separates them out into columns. 

Both rely on setting a record ID, and then tokenizing the field into rows, returning all of the numbers. Super easy to do with theRegEx parse tool. Set the method to Tokenize to rows, and the pattern to \d+ to ignore everything that isn't a number.

Use a summarize to count the number of rows per record ID

Or, assign a unique column with the Tile tool (unique value), and create columns with the crosstab tool.

Let me know if that helps.

Cheers!

Esther