Removing data from first columns and converting it to numbers

Question

I have two columns that I'd like to join but in slightly different formats.

ie. Input 1 = CLMADJ_001, Input 2 = CLMAJD1

Both have commonalities in the numeric figure at the end.

I've managed to cleanse data by removing text at the beginning. However how do I convert the "001" in Input 1 into a normal figure to be able to join with Input 2?

Christine_Assaad · Accepted Answer

Hi @angieeee_w

You can add a Data Cleansing tool to remove letters as well as punctuation which will leave you only the 001 from Input 1. Then you can add a Select tool to convert this field from string to double which should remove leading zeroes.

Hope this helps. Cheers!