If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenation based on conditions
I need to concatenate 3 cells (A1, B1 & E1) into one (G1) and change some
values while doing this. If A1 = 5, I need G1 have B1 (4-digit value with leading zeroes) and A1 as 005 (3 digits with leading zeroes) like this 0131005. Not done yet. if E1 has a value of "CYCLE 3", I need to abbreviate and put "C03" at the end of G1 (0131005C03). I may have up to 99 cycles, each cycle needs to be abbreviated as C plus 2 digits of that cycle. If E1 has a value of "END OF TREAT" I need to put "EOT" at the end of G1 (0131005EOT). If E1 has value of "SCREENING" I need to put "PRS" at the of G1 (0131005PRS). The value of G1 should look like this: B1 A1 and E1 (EOT or PRS or C01 or C02 or C...) 10 positions in total. 1011008C03: B1=1011, A1=8, E1= CYCLE 3 Can anyone help me to get this done? Thank you |
#2
|
|||
|
|||
Concatenation based on conditions
See if this works for you in G1. Remember, it's all one long formula, not
broken into multiple lines. =LEFT("0000",4-LEN(B1))& B1 & LEFT("000",3-LEN(A1)) & A1 & IF(LEFT(E1,5)="CYCLE","C" & TRIM(RIGHT(E1,LEN(E1)-5)),IF(E1="END OF TREAT","EOT",IF(E1="SCREENING","PRS",""))) "Vic" wrote: I need to concatenate 3 cells (A1, B1 & E1) into one (G1) and change some values while doing this. If A1 = 5, I need G1 have B1 (4-digit value with leading zeroes) and A1 as 005 (3 digits with leading zeroes) like this 0131005. Not done yet. if E1 has a value of "CYCLE 3", I need to abbreviate and put "C03" at the end of G1 (0131005C03). I may have up to 99 cycles, each cycle needs to be abbreviated as C plus 2 digits of that cycle. If E1 has a value of "END OF TREAT" I need to put "EOT" at the end of G1 (0131005EOT). If E1 has value of "SCREENING" I need to put "PRS" at the of G1 (0131005PRS). The value of G1 should look like this: B1 A1 and E1 (EOT or PRS or C01 or C02 or C...) 10 positions in total. 1011008C03: B1=1011, A1=8, E1= CYCLE 3 Can anyone help me to get this done? Thank you |
#3
|
|||
|
|||
Concatenation based on conditions
=TEXT(A1,"0000")&TEXT(B1,"000")&IF(E1="END OF
TREAT","EOT",IF(E1="SCREENING","PRS","CYCLE"&TEXT( SUBSTITUTE(E1,"cycle",""),"00"))) Daniel I need to concatenate 3 cells (A1, B1 & E1) into one (G1) and change some values while doing this. If A1 = 5, I need G1 have B1 (4-digit value with leading zeroes) and A1 as 005 (3 digits with leading zeroes) like this 0131005. Not done yet. if E1 has a value of "CYCLE 3", I need to abbreviate and put "C03" at the end of G1 (0131005C03). I may have up to 99 cycles, each cycle needs to be abbreviated as C plus 2 digits of that cycle. If E1 has a value of "END OF TREAT" I need to put "EOT" at the end of G1 (0131005EOT). If E1 has value of "SCREENING" I need to put "PRS" at the of G1 (0131005PRS). The value of G1 should look like this: B1 A1 and E1 (EOT or PRS or C01 or C02 or C...) 10 positions in total. 1011008C03: B1=1011, A1=8, E1= CYCLE 3 Can anyone help me to get this done? Thank you |
#4
|
|||
|
|||
Concatenation based on conditions
Hi Vic
Try =TEXT(B1,"0000")&TEXT(A1,"000")& IF(LEFT(E1)="E","EOT",IF(LEFT(E1)="S","PRS", LEFT(E1)&TEXT(MID(E1,FIND(" ",E1)+1,2),"00"))) -- Regards Roger Govier "Vic" wrote in message ... I need to concatenate 3 cells (A1, B1 & E1) into one (G1) and change some values while doing this. If A1 = 5, I need G1 have B1 (4-digit value with leading zeroes) and A1 as 005 (3 digits with leading zeroes) like this 0131005. Not done yet. if E1 has a value of "CYCLE 3", I need to abbreviate and put "C03" at the end of G1 (0131005C03). I may have up to 99 cycles, each cycle needs to be abbreviated as C plus 2 digits of that cycle. If E1 has a value of "END OF TREAT" I need to put "EOT" at the end of G1 (0131005EOT). If E1 has value of "SCREENING" I need to put "PRS" at the of G1 (0131005PRS). The value of G1 should look like this: B1 A1 and E1 (EOT or PRS or C01 or C02 or C...) 10 positions in total. 1011008C03: B1=1011, A1=8, E1= CYCLE 3 Can anyone help me to get this done? Thank you |
#5
|
|||
|
|||
Concatenation based on conditions
All worked except for Cycles. I get C2 and C4 instead of C02 and C04. How do
I insert a zero if the cycle is only 1 digit long? "JLatham" wrote: See if this works for you in G1. Remember, it's all one long formula, not broken into multiple lines. =LEFT("0000",4-LEN(B1))& B1 & LEFT("000",3-LEN(A1)) & A1 & IF(LEFT(E1,5)="CYCLE","C" & TRIM(RIGHT(E1,LEN(E1)-5)),IF(E1="END OF TREAT","EOT",IF(E1="SCREENING","PRS",""))) "Vic" wrote: I need to concatenate 3 cells (A1, B1 & E1) into one (G1) and change some values while doing this. If A1 = 5, I need G1 have B1 (4-digit value with leading zeroes) and A1 as 005 (3 digits with leading zeroes) like this 0131005. Not done yet. if E1 has a value of "CYCLE 3", I need to abbreviate and put "C03" at the end of G1 (0131005C03). I may have up to 99 cycles, each cycle needs to be abbreviated as C plus 2 digits of that cycle. If E1 has a value of "END OF TREAT" I need to put "EOT" at the end of G1 (0131005EOT). If E1 has value of "SCREENING" I need to put "PRS" at the of G1 (0131005PRS). The value of G1 should look like this: B1 A1 and E1 (EOT or PRS or C01 or C02 or C...) 10 positions in total. 1011008C03: B1=1011, A1=8, E1= CYCLE 3 Can anyone help me to get this done? Thank you |
#6
|
|||
|
|||
Concatenation based on conditions
Roger has given you a much better solution. I was trying to remember how to
do what he did with TEXT() and it just wouldn't come to mind. Glad he was able to come to your rescue. "Vic" wrote: All worked except for Cycles. I get C2 and C4 instead of C02 and C04. How do I insert a zero if the cycle is only 1 digit long? "JLatham" wrote: See if this works for you in G1. Remember, it's all one long formula, not broken into multiple lines. =LEFT("0000",4-LEN(B1))& B1 & LEFT("000",3-LEN(A1)) & A1 & IF(LEFT(E1,5)="CYCLE","C" & TRIM(RIGHT(E1,LEN(E1)-5)),IF(E1="END OF TREAT","EOT",IF(E1="SCREENING","PRS",""))) "Vic" wrote: I need to concatenate 3 cells (A1, B1 & E1) into one (G1) and change some values while doing this. If A1 = 5, I need G1 have B1 (4-digit value with leading zeroes) and A1 as 005 (3 digits with leading zeroes) like this 0131005. Not done yet. if E1 has a value of "CYCLE 3", I need to abbreviate and put "C03" at the end of G1 (0131005C03). I may have up to 99 cycles, each cycle needs to be abbreviated as C plus 2 digits of that cycle. If E1 has a value of "END OF TREAT" I need to put "EOT" at the end of G1 (0131005EOT). If E1 has value of "SCREENING" I need to put "PRS" at the of G1 (0131005PRS). The value of G1 should look like this: B1 A1 and E1 (EOT or PRS or C01 or C02 or C...) 10 positions in total. 1011008C03: B1=1011, A1=8, E1= CYCLE 3 Can anyone help me to get this done? Thank you |
Thread Tools | |
Display Modes | |
|
|