RegEx Parse Comma Separated Numbers

[danielmaguire]

Hi,

I have a data set which as two different formats. I have attached a mock set. I am looking to parse out the third from last and last figures (as denoted by 2s in the mock data). So far, I have used the following regex "\w\s(\w{3})\s\w+\s+\w{3}\s+\d+", however, when I reach the first number, I am having issues as it can differ formats for example 0.05 to 1,234,567.89.

If someone could please assist with the simplest regex to parse both the third from last and last number, that would be greatly appreciated.

Thanks,

Daniel

[HomesickSurfer · Accepted solution]

Hi @danielmaguire 

'GetWord' 🙂

[mceleavey · Accepted solution]

Hi @danielmaguire ,

This is a simple matter of replacing any instance of a double space (\s) with a delimiter (%), then simply text to columns on the delimiter:

M.

[danielmaguire]

Thanks for the rapid replies @mceleavey  and @HomesickSurfer .

@mceleavey I have tried using your solutions which should work great, but I just noticed on the actual data, I am getting a red 'NOT OK' in the final column. Do you know why this may be?

Thanks,

Daniel

[apathetichell]

I think the solutions provided probably work better for your specific problem - but if you want to continue in your regex learning I believe the parse syntax for

0.05 to 1,234,567.89 could be something like ([0-9\,]+\.*\d*) should work. any number of digits and commas - followed possibly by a period (or more unfortunately) followed by more numbers (possibly).

[mceleavey]

HI @danielmaguire ,

I don't see your data, so I have no idea unfortunately.

I guess it is to do with being in a different format to the sample data.

Have a play with the number of columns etc.

M.

[HomesickSurfer · Accepted solution]

Perhaps using GetWord is your solution, assuming that your string data has consistent breaks.

[danielmaguire]

Thanks all for your help!

@mceleavey 

@HomesickSurfer 

@apathetichell