Hi everyone,
I need help in creating new output column.
I want to create new column "month", which taken from the column "name" by using the formula = ToNumber(Right([Name],1)). Input table as below.
Number in the column "name" represents month.
Input table:
Product Code | Country Code | Name | Value |
411140 | JP | Value1 | 25 |
411140 | JP | Value2 | 35 |
411140 | JP | Value3 | 45 |
411140 | JP | Value4 | 60 |
411140 | JP | Value5 | 15 |
411140 | JP | Value6 | 12 |
411140 | JP | Value7 | 13 |
411140 | JP | Value8 | 40 |
411140 | JP | Value9 | 35 |
411140 | JP | Value10 | 33 |
411140 | JP | Value11 | 41 |
411140 | JP | Value12 | 58 |
However, the formula only resulted up to number 9 as shown in output table below.
Output Table:
Product Code | Country Code | Name | Value | Month |
411140 | JP | Value | 25 | 1 |
411140 | JP | Value | 35 | 2 |
411140 | JP | Value | 45 | 3 |
411140 | JP | Value | 60 | 4 |
411140 | JP | Value | 15 | 5 |
411140 | JP | Value | 12 | 6 |
411140 | JP | Value | 13 | 7 |
411140 | JP | Value | 40 | 8 |
411140 | JP | Value | 35 | 9 |
411140 | JP | Value | 33 | 1 |
411140 | JP | Value | 41 | 1 |
411140 | JP | Value | 58 | 2 |
Therefore, is there any way that I can filter the month from "name" column, up until month 12.
Thank you in advance.
Solved! Go to Solution.
Hi @SyafiaSyahirah — You can simply do it by using the formula below:
REGEX_Replace([Name], "[^\d+]", "")
It will just keep the numbers and remove everything else.
And if you want to sort by this new field or use it in a further calculation, then go with a data type as Int64 or Double.
Hi Meera,
It works! Thanks a lot.