Apex Code Development You need to sign in to do that
Don't have an account?
OG Translating Google Sheet formula to Salesforce formula field
hi,
I'm trying to translate the following Google Sheet formula into in custom field formula on a Contact record.
The first step is to translate the value in A2 to hexadecimal, the second step is to reverse the string. The third step is to calculate modulo 17 (the remainder when the number is divided by 17).This number will always be a number from 0–16. Map that number onto the letters A–Q, where 0=A, 1=B, 2=C, etc. Finally, concatenate each of those pieces together.
Many thanks for any help here
I'm trying to translate the following Google Sheet formula into in custom field formula on a Contact record.
=JOIN("", ARRAYFORMULA(MID(DEC2HEX(A2), LEN(DEC2HEX(A2)) - ROW(INDIRECT("1:"&LEN(DEC2HEX(A4)))) + 1, 1)))&CHAR(MOD(A2, 17) + 65)
Where A2 would be a custom field reference.The first step is to translate the value in A2 to hexadecimal, the second step is to reverse the string. The third step is to calculate modulo 17 (the remainder when the number is divided by 17).This number will always be a number from 0–16. Map that number onto the letters A–Q, where 0=A, 1=B, 2=C, etc. Finally, concatenate each of those pieces together.
Many thanks for any help here
"0x" & LEFT( CONVERT( CONVERT( {!A2}, "S", "HEX" ), "HEX", "S" ), -8 ) & TEXT(MOD({!A2}, 17) + 65)
This formula first converts the value in A2 to hexadecimal by using the CONVERT function, then it reverses the string by using the LEFT function with the -8 parameter, then it calculates the modulo 17 by using the MOD function and adds 65 to map the number onto the letters A-Q by using the TEXT function. Finally, it concatenates each of those pieces together.
Please note that {!A2} is the custom field reference, you should replace it with your actual custom field API name.
I just tried to implement your suggestion. Salesforce does not seem to have the function CONVERT, it failed the syntax check because of it. https://help.salesforce.com/s/articleView?id=sf.customize_functions.htm&type=5
Please let me know if I have missed something rergarding the function. Thanks