Mapping account type

SH_94

Dear community,

I would like to ask how do we create formula in order to get the result as below:

If the account start with number of 2 series, then the type will be BS

if the account start with any letters and follow by the number of 2 series , then the type will be BS

if the account start with any letters and follow by the number of 9 series, then the type will be Control

if the account start with any letters and follow by the number of 1 series, then the type will be BS

if the account start with any letters and follow by the number of 3 series, then the type will be BS

If the account start with number of 9 series, then the type will be Control

If the account start with number of 4 series, then the type will be PL

if the account start with any letters and follow by the number of 4 series, then the type will be PL

Thank you

Account type.xlsx

Common Use Cases

Accepted answers

binu_acs

@SH_94 One way of doing this

substring_0928.yxmd

All comments

binu_acs

@SH_94 One way of doing this

substring_0928.yxmd

SH_94

Hi @binu_acs ,

Many thanks for the prompt response. Can u briefly explain how do i read this formula :

IIF(Substring([Account],1,1) IN('1','2','3')

Thank you.

binu_acs

@SH_94 The substring function will return the string based on the start position and the length. In this example, the starting position and the length are 1, so it will return the second letter from the string because the Substring function always starts from 0.

IIF(Substring([Account],1,1) IN('1','2','3') - the substring functions returns the second character from the string Account and checks whether it is in 1,2 or 3

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703