SOLVED

If Function

%3CLINGO-SUB%20id%3D%22lingo-sub-3531418%22%20slang%3D%22en-US%22%3EIf%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3531418%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20suggest%20me%20a%20formula%20for%20the%20following%20excel%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22743px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2244.9688px%22%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2290.9844px%22%3E%3CSTRONG%3EA%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22187.578px%22%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2261.9688px%22%3E%3CSTRONG%3EC%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2258.9219px%22%3ED%3C%2FTD%3E%3CTD%20width%3D%22221.5px%22%3E%3CSTRONG%3EE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2276.0781px%22%3E%3CSTRONG%3EF%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.9688px%22%3E%3CSTRONG%3E1%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2290.9844px%22%3E%3CSTRONG%3EParticulars%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22187.578px%22%3E%3CSTRONG%3ECriteria%201%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2261.9688px%22%3E%3CSTRONG%3EResult%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2258.9219px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22221.5px%22%3E%3CSTRONG%3ECriteria%202%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2276.0781px%22%3E%3CSTRONG%3EResult%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.9688px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2290.9844px%22%3E101010%3C%2FTD%3E%3CTD%20width%3D%22187.578px%22%3EIf%20A2%20starts%20with%201%2C%20C2%20will%20show%20BS%3C%2FTD%3E%3CTD%20width%3D%2261.9688px%22%3EBS%3C%2FTD%3E%3CTD%20width%3D%2258.9219px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22221.5px%22%3EIf%20A2%20starts%20with%201%2C%20F2%20will%20show%20ASSET%3C%2FTD%3E%3CTD%20width%3D%2276.0781px%22%3EASSET%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.9688px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2290.9844px%22%3E201010%3C%2FTD%3E%3CTD%20width%3D%22187.578px%22%3EIf%20A3%20starts%20with%202%2C%20C3%20will%20show%20BS%3C%2FTD%3E%3CTD%20width%3D%2261.9688px%22%3EBS%3C%2FTD%3E%3CTD%20width%3D%2258.9219px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22221.5px%22%3EIf%20A3%20starts%20with%202%2C%20F3%20will%20show%20LIABILITY%3C%2FTD%3E%3CTD%20width%3D%2276.0781px%22%3ELIABILITY%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.9688px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2290.9844px%22%3E301010%3C%2FTD%3E%3CTD%20width%3D%22187.578px%22%3EIf%20A4%20starts%20with%203%2C%20C4%20will%20show%20PL%3C%2FTD%3E%3CTD%20width%3D%2261.9688px%22%3EPL%3C%2FTD%3E%3CTD%20width%3D%2258.9219px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22221.5px%22%3EIf%20A4%20starts%20with%203%2C%20F4%20will%20show%20INCOME%3C%2FTD%3E%3CTD%20width%3D%2276.0781px%22%3EINCOME%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2244.9688px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2290.9844px%22%3E401010%3C%2FTD%3E%3CTD%20width%3D%22187.578px%22%3EIf%20A5%20starts%20with%204%2C%20C5%20will%20show%20PL%3C%2FTD%3E%3CTD%20width%3D%2261.9688px%22%3EPL%3C%2FTD%3E%3CTD%20width%3D%2258.9219px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22221.5px%22%3EIf%20A5%20starts%20with%204%2C%20F5%20will%20show%20EXPENSE%3C%2FTD%3E%3CTD%20width%3D%2276.0781px%22%3EEXPENSE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3531418%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3531461%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3531461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1429671%22%20target%3D%22_blank%22%3E%40Oliullah_Siddique%3C%2FA%3E%26nbsp%3BIn%20C2%20you%20could%20use%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCHOOSE(LEFT(A2)%2C%22BS%22%2C%22BS%22%2C%22PL%22%2C%22PL%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20in%20F2%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCHOOSE(LEFT(A2)%2C%22ASSET%22%2C%22LIABILITY%22%2C%22INCOME%22%2C%22EXPENSE%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BCopy%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3531562%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3531562%22%20slang%3D%22en-US%22%3EThanks%20a%20lot.%20Your%20function%20worked%20very%20well%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3531767%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3531767%22%20slang%3D%22en-US%22%3EDear%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%2C%3CBR%20%2F%3E%3CBR%20%2F%3EWould%20you%20please%20help%20me%20with%20the%20following%20formula%3A%3CBR%20%2F%3EA%20B%20C%3CBR%20%2F%3EParticulars%20Criteria%20Result%3CBR%20%2F%3E1%20101010100000%20If%201st%20digit%20of%20Cell%20A1%20is%201%20and%203rd%20digit%20of%20Cell%20A1%20is%201%2C%20Cell%20C1%20will%20show%20CA%20CA%3CBR%20%2F%3E2%20102020100000%20If%201st%20digit%20of%20Cell%20A2%20is%201%20and%203rd%20digit%20of%20Cell%20A2%20is%202%2C%20Cell%20C2%20will%20show%20NCA%20NCA%3CBR%20%2F%3E3%20103010000000%20If%201st%20digit%20of%20Cell%20A3%20is%201%20and%203rd%20digit%20of%20Cell%20A3%20is%203%2C%20Cell%20C3%20will%20show%20IA%20IA%3CBR%20%2F%3E4%20104010300000%20If%201st%20digit%20of%20Cell%20A4%20is%201%20and%203rd%20digit%20of%20Cell%20A4%20is%204%2C%20Cell%20C4%20will%20show%20INV%20INV%3CBR%20%2F%3E5%20201010100000%20If%201st%20digit%20of%20Cell%20A5%20is%202%20and%203rd%20digit%20of%20Cell%20A5%20is%201%2C%20Cell%20C5%20will%20show%20SE%20SE%3CBR%20%2F%3E6%20202030300000%20If%201st%20digit%20of%20Cell%20A6%20is%202%20and%203rd%20digit%20of%20Cell%20A6%20is%202%2C%20Cell%20C6%20will%20show%20NCL%20NCL%3CBR%20%2F%3E7%20203030300000%20If%201st%20digit%20of%20Cell%20A7%20is%202%20and%203rd%20digit%20of%20Cell%20A7%20is%203%2C%20Cell%20C7%20will%20show%20CL%20CL%3CBR%20%2F%3E8%20301010100000%20If%201st%20digit%20of%20Cell%20A8%20is%203%20and%203rd%20digit%20of%20Cell%20A8%20is%201%2C%20Cell%20C8%20will%20show%20OE%20OE%3CBR%20%2F%3E9%20302020000000%20If%201st%20digit%20of%20Cell%20A9%20is%203%20and%203rd%20digit%20of%20Cell%20A9%20is%202%2C%20Cell%20C9%20will%20show%20Other%20Other%3CBR%20%2F%3E10%20401010200000%20If%201st%20digit%20of%20Cell%20A10%20is%204%20and%203rd%20digit%20of%20Cell%20A10%20is%201%2C%20Cell%20C10%20will%20show%20Exp%20Exp%3CBR%20%2F%3E11%20402010200003%20If%201st%20digit%20of%20Cell%20A11%20is%204%20and%203rd%20digit%20of%20Cell%20A11%20is%202%2C%20Cell%20C11%20will%20show%20FC%20FC%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20a%20lot.%3C%2FLINGO-BODY%3E
Occasional Contributor

Dear Everyone,

 

Please suggest me a formula for the following excel:

 

 ABCDEF
1ParticularsCriteria 1Result Criteria 2Result
2101010If A2 starts with 1, C2 will show BSBS If A2 starts with 1, F2 will show ASSETASSET
3201010If A3 starts with 2, C3 will show BSBS If A3 starts with 2, F3 will show LIABILITYLIABILITY
4301010If A4 starts with 3, C4 will show PLPL If A4 starts with 3, F4 will show INCOMEINCOME
5401010If A5 starts with 4, C5 will show PLPL If A5 starts with 4, F5 will show EXPENSEEXPENSE

 

Thanks in advance.

2 Replies
best response confirmed by Oliullah_Siddique (Occasional Contributor)
Solution

@Oliullah_Siddique In C2 you could use:

=CHOOSE(LEFT(A2),"BS","BS","PL","PL")

and in F2:

=CHOOSE(LEFT(A2),"ASSET","LIABILITY","INCOME","EXPENSE")

 Copy down

Thanks a lot. Your function worked very well @Riny_van_Eekelen.