What is the best why to change the format of data in a cell?

kim_barragan0126

I have a data set that brings in names under the column BDM Name with FY 2024 at the end of the name, see below screenshot. What is the best way to remove the "FY2024?" I have about 8 BDM names I need to change. Should I use a replace text tile in ETL?

ArborRose

You can use a replace text tile in an ETL to remove the known value, "FY2024". Or a command like this if it's static:

TRIM(SUBSTRING(`BDM Name`, 1, LENGTH(`BDM Name`) - LENGTH('FY2024')))

Or get a be more complicated using a regular expression in a formula:

REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', '')

and wrap the TRIM command in there for spaces

TRIM(REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', ''))

ArborRose

You can use a replace text tile in an ETL to remove the known value, "FY2024". Or a command like this if it's static:

TRIM(SUBSTRING(`BDM Name`, 1, LENGTH(`BDM Name`) - LENGTH('FY2024')))

Or get a be more complicated using a regular expression in a formula:

REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', '')

and wrap the TRIM command in there for spaces

TRIM(REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', ''))

kim_barragan0126

Thank you! The command worked great.