Code generation formula

[rameshppc]

[TABLE="width: 500"]
<tbody>[TR]
[TD]S NO
[/TD]
[TD]CODE NUMBER
[/TD]
[TD]MOBILE NUMBER
[/TD]
[TD]REFERING NUMBER
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]6375871156
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi good morning,

I need your help for making the above chart as smart.

I am giving a offer to my customers, who are purchasing mobile phones in our shop.

I want to give a unique offer code to each mobile phone purchasing customer and tel them to refer some one to purchase mobile in our shop by bring the offer code.

Now i will fill the "c" & "d" column with the customer mobile number, and i need a code "b" column.

The code generation will be as like,
if, the mobile number is 6375871156
Then, first three number 6+3+7 = 16
then, next three number 5+8+7 = 20
then, next three number 1+1+5 = 7
last one digit "6" = F (6th digit in alphabet)
Now i need the code as 16207F

Hope you will understand what i am expecting. I am ready to explain you more if my above request not clear for you.

Advance thanks.

 

[shaowu459]

=SUM(--MID(LEFT(A1,3),{1,2,3},1))&SUM(--MID(MID(A1,4,3),{1,2,3},1))&SUM(--MID(MID(A1,7,3),{1,2,3},1))&CHAR(64+RIGHT(A1))

ctrl shift enter run the formula.

 

[rameshppc]

Hi i am getting #value ! even i use ctrl shift enter.

I think the "a1" you mentioned is the phone number I wrote in "c2" right?

 

[shaowu459]

yes, A1 in my formula means the cell contains the phone number.

 

[shaowu459]

=sum(--mid(left(c2,3),{1,2,3},1))&sum(--mid(mid(c2,4,3),{1,2,3},1))&sum(--mid(mid(c2,7,3),{1,2,3},1))&char(64+right(c2))

 

[mse330]

shaowu459 formula is working fine & neat. If you wish to avoid using array formula you can use the below

=MID(C2,1,1)+MID(C2,2,1)+MID(C2,3,1)&MID(C2,4,1)+MID(C2,5,1)+MID(C2,6,1)&MID(C2,7,1)+MID(C2,8,1)+MID(C2,9,1)&CHAR(64+RIGHT(C2))

 

[shaowu459]

shaowu459 formula is working fine & neat. If you wish to avoid using array formula you can use the below

=MID(C2,1,1)+MID(C2,2,1)+MID(C2,3,1)&MID(C2,4,1)+MID(C2,5,1)+MID(C2,6,1)&MID(C2,7,1)+MID(C2,8,1)+MID(C2,9,1)&CHAR(64+RIGHT(C2))

Yes, normal formula is OK.

In office 365, we can also use CONCAT or TEXTJOIN:

=CONCAT(MMULT(--MID(C2,{1,2,3}+{0;3;6},1),{1;1;1}))&CHAR(64+RIGHT(C2))

 

[rameshppc]

Its working awesome.. you are great... thanks you again..