Need help

[anonymous008_G]

Hi There,

How can I create new column basis on my input column? Below is my input column and i want to create expected output column using formula tool. Please help me with formula. 

I did try below formula but didn't work as expected.

if Contains([Name], "1") then "1Y" ELSEIF Contains([Name], "2") then "2Y" ELSEIF Contains([Name], "3") then "3Y" ELSEIF Contains([Name], "4") then "4Y" ELSEIF Contains([Name], "5") then "5Y" ELSEIF Contains([Name], "7") then "7Y" ELSEIF Contains([Name], "10") then "1 Y" ELSE [Name] ENDIF

Input	Expected Output
1 Yr To 2023-12-31	1Y
2 Yrs* To 2023-12-31	2Y
3 Yrs* To 2023-12-31	3Y
4 Yrs* To 2023-12-31	4Y
5 Yrs* To 2023-12-31	5Y
10 Yrs* To 2023-12-31	10Y
1 Yr To 2014-12-31	1Y
1 Yr To 2015-12-31	1Y
1 Yr To 2016-12-31	1Y
1 Yr To 2017-12-31	1Y
1 Yr To 2018-12-31	1Y
1 Yr To 2019-12-31	1Y
1 Yr To 2020-12-31	1Y
1 Yr To 2021-12-31	1Y
1 Yr To 2022-12-31	1Y
1 Yr To 2023-12-31_2	1Y

[DataNath · Accepted solution]

Hey @anonymous008_G, this isn't working/you'll just get '1Y' for all of the results as the if checks work sequentially i.e. will check for 1 first, then 2 if that's not satisfied etc. - In your case, you have a 1 in all of your records because of the dates and so it stops at that first check.

If you want to just take the number of years at the very start and add a 'Y' which it looks like you're trying to do, you can do so pretty simply with a Formula expression such as:

GetWord([Name], 0)+'Y'

[anonymous008_G]

Great Solution thanks. these are very good learnings from future prospect. Also, could you please help with one more requirement where from same input column i want to extracts dates into new column. What could be best solution for this? Regex_replace formula?

[DataNath · Accepted solution]

No problem @anonymous008_G - always happy to help! With the date requirement, there's a few ways you could go about this and REGEX_Replace() is definitely one. If the string format is fairly consistent/won't have anything else that holds an ISO date-like pattern then you could just add an expression like this:

REGEX_Replace([Name], '.*(\d{4}-\d{2}-\d{2}).*', '$1')

[anonymous008_G]

Thank you so much for this. I did try this formula, but only .* was missing, and hence it was not working for me. 

Thanks again appreciated your help.