Date cleanse. Trim.

[douglasgregory418]

Have a column of V String that I'm trying to isolate 4 digit number from. Some have punctuation, letters, or unnecessary zeroes. 

Started with data cleanse that removed all punctuation and letters, leaving only leading zeroes. 

Currently trying to use select to convert from vstring to int64 (or 32 or 16) then use formula

IF LEFT([column],2)="00"

THEN TRIMLEFT([Column],2)
ELSE **ideally do nothing, but currently "TRIMRIGHT([Column],00
ENDIF

It keeps telling me that there is a type mismatch. Number provided where a string is required. 

I'm new to Alteryx and not quite sure what to do.

Thanks for the help. 

[douglasgregory418]

Have a column of V String that I'm trying to isolate 4 digit number from. Some have punctuation, letters, or unnecessary zeroes. 

Started with data cleanse that removed all punctuation and letters, leaving only leading zeroes. 

Currently trying to use select to convert from vstring to int64 (or 32 or 16) then use formula

IF LEFT([column],2)="00"

THEN TRIMLEFT([Column],2)
ELSE **ideally do nothing, but currently "TRIMRIGHT([Column],00
ENDIF

It keeps telling me that there is a type mismatch. Number provided where a string is required. 

I'm new to Alteryx and not quite sure what to do.

Thanks for the help. 

[derekbelyea]

Use the tonumber() function to transform strings to numbers.

[Claje]

Hi,

Your problem is in the TRIMRIGHT([Column],00) portion.

If you change this to TRIMRIGHT([Column],"00") it should fix the problem.

Alternatively, if you want the column to be left alone in this instance, if you replace this piece entirely with [Column] that will work

[douglasgregory418]

I'm not seeing a "tonumber()" in any of the function areas or the search. 

[douglasgregory418]

Thanks, but this did not solve the Type Mismatch problem, which seems to be the real problem. Appreciate the help.

[derekbelyea]

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Converting-strings-to-numbers/ta-p/25582

[danrh · Accepted solution]

If I understand correctly, you're converting to an INT64 and then using the formula you provided, correct?  The issue is that this is no longer a string, so you can't use left/trimleft/etc. on it.  Try reversing your order and see if that helps.  For example, after a Data Cleansing tool but before changing the type to an integer, use:

Left(TrimLeft([Field1],'0'),4)

Then drop a Select tool and change it to an INT64.  Works on my end, let me know if it helps.

[douglasgregory418]

Thanks.