REGEX_Replace

[coren]

I am trying to use a formula to grab the Quarter/Year of a string from a filename.  It gets tricky because I have multiple files but the names are not always the same length.  For instance, consider the following files:

CAJCD_Q116_12345.txt

CA_Q215_ABCDE.txt

ABCD_Q313_AAAA.txt

I would need to grab the "Qxxx" from each of those.  I tried using REGEX_REPLACE but can't seem to figure out the appropriate formula.  Can I use REGEX_REPLACE for this or is there an easier/different way?

[MarqueeCrew · Accepted solution]

how about:

Regex_Replace([Fieldname],"(.*_)(Q\d{3})(_.*)","$2")

This will output the 2nd grouping (things inside of a parenthesis set).  It looks for:

(Anything followed by an underscore)(Q followed by 3 digits)(Underscore followed by anything).

If the Q value is 1,2, or 3 digits long, you can use (Q\d{1,3}) as the second group.

Maybe this will help you.  Use it in a formula where you create a new variable, Quarter and define it as a Text (String) variable.

Cheers,

Mark

[Federica_FF]

If the string is always made by 3 parts and the Qxxx part is always the second one:

part1_Qxxx_Part3

you can also go easier with a text to colum that creates 3 columns on each _ and only keep the second column.

[MarqueeCrew]

@Federica_FF,

I agree, but if the underscore isn't always there then you can modify the expression as:

(.*)(Q\d{3})(.*) and find it.

Simplicity is nice in your solution if the dataset names are consistent.

[coren]

Awesome!  This worked perfectly.  Thanks so much!

[Amarendra]

Hello, 

I have a file with address information. There are multiple fields related to the address, and are in the below format 

Current format 

City | State | Country 

City, State, Country 

Blank, City, State - Some of the states fields have city information and same for country. 

Country, Blank, State 

As seen above,  the addresses are not consistent and some of the fields are missing all-together.

Can I know how can I use RegEx to get all my strings into one consistent format? I basically have to swap fields and get all of them into one format.

Required Format 

City | State | Country 

City, State, Country 

City, State, Country 

City, State, Country