Concatenation

  • Thread starter Thread starter Vic
  • Start date Start date
V

Vic

I need to produce column C by concatenating A and B.
C must always have 10 alphanumerics.
A always consists of 7 numerics.
B may have different value:
numbers from 1 to 30 should convert to C01 to C30,
letter C followed by a number from 1 to 30 (C1 to C30) should convert to C01
to C30,
letter C followed by a 2 digit number (C01 to C30) no conversion needed,
letter P should convert to PRS,
value ES shold convert to EOS.
What is the fornula to do this?
Thank you.
 
I need to produce column C by concatenating A and B.
C must always have 10 alphanumerics.
A always consists of 7 numerics.
B may have different value:
numbers from 1 to 30 should convert to C01 to C30,
letter C followed by a number from 1 to 30 (C1 to C30) should convert to C01
to C30,
letter C followed by a 2 digit number (C01 to C30) no conversion needed,
letter P should convert to PRS,
value ES shold convert to EOS.
What is the fornula to do this?
Thank you.
Click to expand...

Perhaps:

=IF((A1&B1)<>"",TEXT(A1,"0000000")&SUBSTITUTE(
SUBSTITUTE(TEXT(B1,"\C00"),"P","PRS"),"ES","EOS"),"")

--ron
 
It only took care of P and ES.
My major task is to convert C1 thru C9 to C01 thru C09 and converting 1 thru
30 to C01 thru C30. How can I do this?
Thanks.
 
Hello,

Copy into J1:K41 these values
1 C01
2 C02
3 C03
4 C04
5 C05
6 C06
7 C07
8 C08
9 C09
10 C10
11 C11
12 C12
13 C13
14 C14
15 C15
16 C16
17 C17
18 C18
19 C19
20 C20
21 C21
22 C22
23 C23
24 C24
25 C25
26 C26
27 C27
28 C28
29 C29
30 C30
C1 C01
C2 C02
C3 C03
C4 C04
C5 C05
C6 C06
C7 C07
C8 C08
C9 C09
P PRS
ES EOS

Then enter into C1:
=A1&IF(ISERROR(MATCH(B1,$J$1:$J$41,0)),B1,VLOOKUP(B1,$J$1:$K$41,2,0))
and copy down.

Regards,
Bernd
 
=A1&IF(B1="P","PRS",IF(B1="ES","EOS",IF(LEFT(B1)="C",IF(LEN(B1)=2,"C0"&RIGHT(B1),B1),IF(ISNUMBER(B1),"C"&TEXT(B1,"00"),""))))
 
Back
Top