Take only numeric characters or Only lines with N characters

max_hfr

Hi,

I am having an issue separating different types of data in the same column.

The column looks like this:

CMA 201802

CMA 201803

CF 201802

CF 201804

201804

201806

201807

I want to put CMA, CF, and month (201804) in different columns.

I was able with if contains "cma" or "cf" but for the month i cannot use contain since the other ones contain the same data.

Is there a formula to either only take lines with numeric value or only take the lines which are 6 characters long.

PS: I don't want to trim any of the cma / cf.

Thank you in advance,

Max

Regex

Interface Tools

Accepted answers

Thableaus

Hi @max_hfr

Parsing with Regex would work fine.

(\D*)(\d+)

Not a digit (0 or more times) / digit 1 or more times.

If your data may look different in the future, let me know so we can adjust RegEX.

Cheers,

All comments

MarqueeCrew

@max_hfr ,

I can read your requirements different ways. Can you please post your desired output too?

Cheers,

Mark

Thableaus

Hi @max_hfr

Parsing with Regex would work fine.

(\D*)(\d+)

Not a digit (0 or more times) / digit 1 or more times.

If your data may look different in the future, let me know so we can adjust RegEX.

Cheers,

max_hfr

I wasn't orginally thinking of it this way but it could be even better.

I actually have more diffrences here are all the possible formats:

CMA 201802

Cuf 201802

Bimestre 201802

Cma 201802

Semestre 201802

Trim 201802

I assume it works with this regex

thank you in advance

mbarone

Probably lots of ways to do this, some might even be more efficient than mine, but here's what I did:

I recreated your sample using a text tool, and then dropped a regex tool after it:

2019-03-08 09_23_24-Alteryx Designer x64 - New Workflow1_.jpg 2019-03-08 09_23_15-Alteryx Designer x64 - New Workflow1_.jpg

I parsed out the column like this:

2019-03-08 09_23_07-Alteryx Designer x64 - New Workflow1_.jpg

Which splits your data like this:

2019-03-08 09_22_52-Alteryx Designer x64 - New Workflow1_.jpg

Next I would trim the RegExOut1 filed to remove the extra space at the end.
Then, assuming you want the numeric only values in Column1 to to into the RegExOut2 column, I would simply update that field with the formula IIF(IsNull([RegExOut3),[Column1],[RegExOut3]).

At that point you can create two fields where one is Left([RegExOut3],4) and one is Right([RegExOut3],2).

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703