Parsing Text

[samxhander09]

Hi, Im new here and im wondering how can I parse the below without having to use 3 regex tool?

expressions for parsing 

(764\d{7})

(615\d{7})

(792\d{7})

SAMPLE VALUES TO PARSE
7642313537
6156701028
7926589011
7642313874
7926589000
6156549028
6151000002
7642333879

Thank you in advanced!

[Clifford_Coon]

Hi Sam,

Here's a thought:

If it's always the first 3 characters, you can use a string formula.

String:        tonumber(left([Value],3))

Number:    tonumber(left(tostring([Value]),3))

[SPetrie]

(764\d{7}|615\d{7}|792\d{7})

The | character is an or, so its saying match this, or this, or this.

[samxhander09]

Hi guys, appreciate the reply, I was wondering if this would parse the data into three columns? just like when I use 3 regex tool?

like for column 1 it would have all data that start with 764 and so on for column 2 those 615 and column three 792?

[SPetrie · Accepted solution]

Also possible with one tool. A light adjustment to the expression to make each option its own capture group

(764\d{7})|(615\d{7})|(792\d{7})

then a bit of setup for the columns in the regex tool

[danilang · Accepted solution]

Hi @samxhander09 

Here's a dynamic, non-regex way to to group and crosstab your values

The Keys input contains the key (764,615,etc) and a field for the column number.  Use a Find Replace tool configured to match the start of the field and append the Column Number field.  Sort, add row numbers and crosstab for the result.  The Dynamic Rename replaces the column numbers with the keys to give 

To make this dynamic, change the Keys input to a Input Data tool and keep the list of keys and columns in a shared location.  

Dan

[samxhander09]

Hi, thank you for the massive help! Was able to get the desired output. Have a great day!