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!
Solved! Go to Solution.
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
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
Another way that you may get those numbers out is with the following expression:
([\d\,]{4,}\.\d\d)
This will look for:
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.