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

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

=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

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

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

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