How to use if statement to output data into specific format

[nkapadia]

I have file with TAX ID data in XXXXXXXXX format from Input file where I need it in specific format in output file. Such as Individual Tax id in XXX-XX-XXXX format and FEIN in XX-XXXXXXX. I have a field called Owner is company. Based on this field, how I should write the If formula that will return output TAX id XX-XXXXXXX if Owner is company is “Y” else XXX-XX-XXXX.

How do I put below statement using IF then Else statement

Left([OwnerTaxID],3)+"-"+Substring([OwnerTaxID],4,2)+"-"+right([OwnerTaxID],4)

Left([OwnerTaxID],2)+"-"+right([OwnerTaxID],7)

[ShankerV]

Hi @nkapadia 

One way of doing this.

IF [Owner]="Company"
THEN Left([OwnerTaxID],2)+"-"+right([OwnerTaxID],7)
ELSE Left([OwnerTaxID],3)+"-"+Substring([OwnerTaxID],4,2)+"-"+right([OwnerTaxID],4)
ENDIF

Many thanks

Shanker V

[nkapadia]

Thank you. I originally wrote as below but it doesn't generate the result for the FEIN XX-XXXXXXX when OwnerIsCompany = Y. OwnerIsCompany has Y or N value

[ShankerV]

Hi @nkapadia 

Can you please explain as you have applied the formula OwnerTaxID but expecting result to be applied on FEIN.

Many thanks

Shanker V

[nkapadia]

Hello Shanker,

Below is my Input 

Want to generate the output on OwnerTaxID field based on OwnerIsCompany criteria

[ShankerV · Accepted solution]

Hi @nkapadia 

One way of doing this.

IF ISnull([OwnerCompanyName])
THEN "N"
ELSE "Y"
ENDIF

IF [OwnerIsCompany]="Y"
THEN Left([OwnerTaxID],2)+"-"+right([OwnerTaxID],7)
ELSE Left([OwnerTaxID],3)+"-"+Substring([OwnerTaxID],4,2)+"-"+right([OwnerTaxID],4)
ENDIF

Many thanks

Shanker V

[nkapadia]

Thank you so much for the thorough solution.