• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding more conditions in an excel formula

Y

yan nian

New Member
I believed the condtions written will be quite long and i am not really good in writing this long formula There are 6 columns i've used which is D ,E, M, N, O, P
Sample data:

D3=123456(Changing variable as it can be 12345, 12345A,123456A)
E3=1
M3=31
N3=_
O3=00
P3=0
The formula are design based on this Column D field(the variable changes is in this field) let say if length of D3 = 6 then (the current formula i've done)

=IF(LEN(D3)=6,CONCATENATE(M3,D3,N3,O3,E3),CONCATENATE(M3,D3,O3,E3))

The outcome for this will be 31123456_001, if let say the D variable is changed to 123456A( the else in the formula i've shown as no concatenate N3) then the outcome will be 31123456A001.

I have added in column p, so that i can use it to concatenate to the format that i need. There are a few more conditions i need to add in, Which is
1. If the D3= 12345, the format outcome will be 31012345_001 (concatenate M3,P3,D3,N3,O3,E3)
2. If the D= 12345A, the format outcome will be 31012345A001 (concatenate M3,P3,D3,O3,E3)
3. Data for the column D3 field, 12345A, the A alphabet can be in A-Z.
 
Hi:

As per your uploaded workbook the following formula will give you what you are looking for.

=IF(ISNUMBER(RIGHT(D3,1)*1),M3&D3&N3&O3&E3,M3&D3&O3&E3)

Thanks
 
hi nebu,
It's almost there.
it did fulfilled
1. D2 = 137315 then the output is 31137315_001
2. D3 =137315A then output is 31137315A001
but it didn't fulfilled the conditions 3 and 4
3. D4 = 13731 then the output is 31013731_001 ( there's a 0 in P column which i use to concatenate)
4. D5 = 13731A then the output is 31013731A001( there's a 0 in P column which i use to concatenate)

sorry that i didn't state clearly.
 
Hi:

Try the below formula

=IF(AND(LEN(D2)>=6,ISNUMBER(RIGHT(D2,1)*1)),M2&D2&N2&O2&E2,IF(AND(LEN(D2)<6,ISNUMBER(RIGHT(D2,1)*1)),M2&P2&D2&N2&O2&E2,IF(AND(LEN(D2)=6,ISTEXT(RIGHT(D2,1))),M2&P2&D2&O2&E2,M2&D2&O2&E2)))

Thanks
 
Hi check if this helps,

=CHOOSE(LEN(D3)-4,CONCATENATE(M3,P3,D3,N3,O3,E3),IF(ISNUMBER(RIGHT(D3,1)*1),CONCATENATE(M3,D3,N3,O3,E3),CONCATENATE(M3,P3,D3,O3,E3)),CONCATENATE(M3,D3,O3,E3))

It works if D3 number portion is either 5 or 6 digits and either followed by A to Z or not.

with best regards,
Arun N
 
You must log in or register to reply here.
Back
Top