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. 

pedrodrfaria

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.