Removing data from first columns and converting it to numbers

[angieeee_w]

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?

[treepruner]

Could you just create a new field that is an integer and to ToNumber([Field1]) on your original field? Here is an example

You also might be able to use one of the replace functions if there are always leading zeros 

https://help.alteryx.com/current/designer/string-functions

[christine_assaad · Accepted solution]

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!