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
Solved! Go to Solution.
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,
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
It works fine, as long as the "word" part doesn't have any numbers on it.
If there's always a space between the word and the number (month), you could be more specific
(\D*?)\s*(\d{6})
So you don't need to trim the word after that. But that's up to you.
Cheers,
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:
I parsed out the column like this:
Which splits your data like this:
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).
Hi Thanks a lot, it's even better.
I have one last problem
I have
CMA and Cma. I would like to transform Cma into CMA so all of them are written the same.
I have tried a formula if "Cma" then "CMA" but that doesn t seem to work.
Any idea?
Yes, in your formula where you trim the white space, just wrap it in the function "Uppercase()".
Is it just CMA you want to uppercase?
If not, you could use function Uppercase([Field]) and you would uppercase all of your strings.
Or your IF condition (specific to CMA) could be this way:
IF Contains([Field], "Cma") THEN "CMA" ELSE [Field] ENDIF
I'd use Contains function in case you have any extra whitespaces, dashes or something like that.
Cheers,
Hi Thableaus,
you're right it was because of additional spaces. Thanks a lot for all your help