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

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

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

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

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

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