Search code examples
alteryx

Alteryx separate the first integer of a number and put it in a new column

I only have one column that has an 8-digit number. No dots, commas or any conjugation. Only integers. I simply want to extract the first integer from the number and put it to a new column named "First integer". I want the rest of the integers, untouched, to either go to a new column as they are, or stay in the existing column but without the first integer

for example now I have: columnA: 23456789

I want First Integer:2 columnA: 3456789

I am pretty new to Alteryx so that might even be a ridiculous question to some :P But any help is greatly appreciated :)

Solution

  • Suppose [i] is the relevant numeric field in your Alteryx workflow.

    Then using a Formula tool, this expression will give the first digit:

    [i]/POW(10,FLOOR(LOG10([i])))

    And this will give the remaining digits:

    MOD([i],POW(10,FLOOR(LOG10([i]))))

    Explanation: working inside-out: Log10([i]) tells you how many powers of 10 you're working with, FLOOR just rounds that off, POW(10,...) multiplies it back out... so basically for an 8 digit number, this gives 10,000,000. Then you simply divide to get the first digit, or take the MOD (modulus) to get the remaining digits.

    PS, your question mentions an 8-digit integer... if you are absolutely certain that your integers always have 8 digits (and the first digit is not a zero), then you can shortcut this: firstDigit=[i]/10000000 and remainingDigits=MOD([i],10000000).