Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parsing a field based on the number of white spaces

rbrown088
5 - Atom

Hi all, I am new to Regex and can normally use text to columns to work around having to formulate the expression- I am having trouble with text-to-columns with my current data and am giving Regex a shot.

 

I am trying parse 3 numeric values from a string that may vary in the amount of millions (100M, 10M, 1M, etc.) and may be below the 1M mark depending on the item. I am able to extract the first number in the dummy data below with the following regular expression:

 

(\s\d\d\W\d\d\d\W\d\d\d\W\d\d)

 

The regular expression fails anytime the number to be returned is not in the 11,111,111,11 format. Ideally I would prefer to extract all three of these numbers and assume I could text to columns to separate these further.

 

Is there any way to extract everything to the right of the 4th white space after the date? [See dummy data below for example]

 

XXXX X08653 3.000 11/11/11 20,000,000.00 14,000,000.00 14,000,000.00

 

Thanks!

3 REPLIES 3
fmvizcaino
17 - Castor
17 - Castor

Hi @rbrown088 ,

 

One way to extract everything after the 4th space is by using (.*) that means you want to extract any character multiple times.

Also, if you want to separate each of the 3 numbers, you can use ([^\s]+)\s([^\s]+)\s([^\s]+)

Which means you are getting everything you want but a space character repeated 3 times.

 

Let me know if that works for you.

Best,

Fernando Vizcaino 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@rbrown088 ,

 

I used this regular expression:

 

.*\/\d+\s([\d,\.]+)\s([\d,\.]+)\s([\d,\.]+)

I also cleaned up the numbers to make them NUMBERS and not text (removed commas).

 

Cheers,

 

Mark 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
KaneG
Alteryx Alumni (Retired)

Another way that you may get those numbers out is with the following expression:

([\d\,]{4,}\.\d\d)

This will look for:

  • at least 4 characters that are either digits or commas
  • then a decimal
  • followed by 2 digits

Normally you would include a RecordID beforehand, REGEX - Tokenise: Split to Rows, Tile for sequence.

 

If you need to explore any of the solutions in more detail, then you can play around with them at Regex101.com.

Labels