Need help creating new column using Formula tool

anonymous008_G

Hi there,

Can anyone please advise how I would convert my column into a new formatted column using the formula tool?

Below is sample data. I am not very much familiar with formulas which can be used here to convert into new format.

Below is the sample data and New column is the expected data from Period column.

Period	Value		New Column
One Year Ended 12/31/23	1		1Y 12/31/2023
One Year Ended 12/31/22	2		1Y 12/31/2022
One Year Ended 12/31/21	3		1Y 12/31/2021
One Year Ended 12/31/20	4		1Y 12/31/2020
One Year Ended 12/31/19	5		1Y 12/31/2019
One Year Ended 12/31/18	6		1Y 12/31/2018
One Year Ended 12/31/17	7		1Y 12/31/2017
One Year Ended 12/31/16	8		1Y 12/31/2016
One Year Ended 12/31/15	9		1Y 12/31/2015
One Year Ended 12/31/14	10		1Y 12/31/2014
One Year Ended 12/31/23	11		1Y 12/31/2023
Two Years Ended 12/31/23	12		2Y 12/31/2023
Three Years Ended 12/31/23	13		3Y 12/31/2023
Four Years Ended 12/31/23	14		4Y 12/31/2024
Five Years Ended 12/31/23	15		5Y 12/31/2024
Ten Years Ended 12/31/23	16		10Y 12/31/2025

sergejs_kutkovics

Hi, @anonymous008_G. For sure, you can use formula tool to reate a conditional expression like 

Spoiler

IF Contains([Period], "One Year") THEN
  '1Y ' + Right([Period], 10)
ELSEIF Contains([Period], "Two Years") THEN
  '2Y ' + Right([Period], 10)
ELSEIF Contains([Period], "Three Years") THEN
  '3Y ' + Right([Period], 10)
ELSEIF Contains([Period], "Four Years") THEN
  '4Y ' + Right([Period], 10)
ELSEIF Contains([Period], "Five Years") THEN
  '5Y ' + Right([Period], 10)
ELSEIF Contains([Period], "Ten Years") THEN
  '10Y ' + Right([Period], 10)
ELSE
  [Period]
ENDIF

Though, I'd recommend creating a mapping dictionary of values to replace and using the Find/Replace tool to replace text with numbers. See the attached workflow.

Hope it helps!

new column using Formula tool.yxmd