RegEx Question

[anovohatski1]

Hi All,

I am stuck, so hoping that someone can help since I am fairly new to Regex. I have two types of strings.

Type 1: AAA1110000000000AAA AAA             0.0000 0.0000        0.0000N      0.0000           0.0000 0.00000

I got the following regex string to work when using RegEx tool

([A-Z]+)111(\d+)([\w\W]+)\s+(\d+.\d+)\s+(\d+.\d+)\s+(\d+.\d+)([A-Z]+)\s+(\d+.\d+)\s+(\d+.\d+)\s+(\d+.\d+)

However, it fails on the second type of string, and I have no idea how to make a regex that would work on both.

Type 2: AAA1110000000000N/A             0.0000 0.0000        0.0000N      N/A           N/A N/A

Please note that I have tried to use Text to Column, but because there is no delimiter - it wouldn't work. 

[OllieClarke]

Hi @anovohatski1 if those strings are representative, then it looks like you can use whitespace as a delimiter. If you put your RegEx tool in Tokenize mode, and split to 7 columns, then the following RegEx should work:

\S+

(note that that's a capital S). This will tokenise out all non-space characters into their own column. 

Hope that helps,

Ollie

[pedrodrfaria · Accepted solution]

Hi @anovohatski1 

For this specific instance, this regex function would do the trick:

([A-Z]+)111(\d+)([\w\W]+)\s+(\d+.\d+)\s+(\d+.\d+)\s+(\d+.\d+)([A-Z]+)\s+(\d+.\d+)*\s+(\d+.\d+)*\s+(\d+.\d+)* 

Or maybe you are looking for something like this:

([A-Z]+)111(\d+)([\w\W]+)\s+(\d+.\d+)\s+(\d+.\d+)\s+(\d+.\d+)([A-Z]+)\s+(\d+.\d+|N/A)\s+(\d+.\d+|N/A)\s+(\d+.\d+|N/A)

Pedro.

[anovohatski1]

@OllieClarke Thank you for the suggestion. I have tried it; however, this didn't solve my problem as I want AAA AAA to be pulled together into 1 column, rather than 2. 

[anovohatski1]

@pedrodrfaria Thank you. The first expression worked exactly like i wanted it to.