# Combined Coded letters in one cell

#### Shaji Panchali

##### New Member
Hi..
I need help in excel data entry. I have a sheet in which under product code column, i need to enter letters as a part of code for my products and the assigned value to appear in the next cell.
Example: If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0

In cell E2 if i enter "PRN", in cell F2 i should get "350" or
In cell E3 if i enter "OR", in F3 i should get "75" or
In cell E4 if i enter "SU", in F4 i should get "12"
What will be formula to enter in cells F2, F3 & F4 ?? (Highlighted cells in attachment file)

#### Attachments

• 11.5 KB Views: 12

#### pecoflyer

##### Active Member
Could you please explain the logic? Looks quite obscure how for example PRN becomes 350 ?

#### Shaji Panchali

##### New Member
I have coded each product with a single letter or two or three.. for which if i need to enter a product code "PRN" then the coded value for the same should be 350. If the values assigned is as follows :If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0
You can check the sample file attached.

#### Deepak

##### Excel Ninja
Shaji Panchali

If i understand this!!!
If your excel version support textjoin function then check it.

#### Attachments

• 12.4 KB Views: 8

#### salim hasan

##### New Member
Use This Macro
File Attached
Code:
``````Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim My_Num
If Target.Column = 5 And Target.Row > 1 _
And Target.Cells.Count = 1 Then
Select Case UCase(Target.Value)
Case "PRN": My_Num = 350
Case "OR": My_Num = 75
Case "SU": My_Num = 12
Case Else: My_Num = vbNullString
End Select
Target = UCase(Target)
Target.Offset(, 1) = My_Num
End If
Application.EnableEvents = True
End Sub``````

#### Attachments

• 17.3 KB Views: 6

#### pecoflyer

##### Active Member
Sorry I don't undrestand. Hope someone else will be able to help

#### Shaji Panchali

##### New Member
Shaji Panchali

If i understand this!!!
If your excel version support textjoin function then check it.

Textjoin is for collating texts from different cells. But i have assigned each text for values 0 to 9 as below...
0 = K
1 = M
2 = Y
3 = S
4 = O
5 = R
6 = E
7 = B
8 = A
9 = N

Now, if my product cost is 350, i need to enter in coded letters, like PRN (means 350)
For which I'm struggling to fetch a formula for the same.
Check attachment for more details...

#### Shaji Panchali

##### New Member
Use This Macro
File Attached
Code:
``````Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim My_Num
If Target.Column = 5 And Target.Row > 1 _
And Target.Cells.Count = 1 Then
Select Case UCase(Target.Value)
Case "PRN": My_Num = 350
Case "OR": My_Num = 75
Case "SU": My_Num = 12
Case Else: My_Num = vbNullString
End Select
Target = UCase(Target)
Target.Offset(, 1) = My_Num
End If
Application.EnableEvents = True
End Sub``````
Thanks.. Salim..
Can't we do it without using vba.. just excel formula

Maybe like this

#### Attachments

• 10 KB Views: 6

#### NPOKALA

##### New Member
Hi..
I need help in excel data entry. I have a sheet in which under product code column, i need to enter letters as a part of code for my products and the assigned value to appear in the next cell.
Example: If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0

In cell E2 if i enter "PRN", in cell F2 i should get "350" or
In cell E3 if i enter "OR", in F3 i should get "75" or
In cell E4 if i enter "SU", in F4 i should get "12"
What will be formula to enter in cells F2, F3 & F4 ?? (Highlighted cells in attachment file)

First of all, you need to share the Table: "Product code" along with there corresponding "Cost value(s)".

then, its a simple formula which can assign a "cost Value" whenever a Product Code is entered and rest as simple as it gets.

#### Shaji Panchali

##### New Member
That's where the catch is...

Each product has a different code and everytime a new product is added.
Sometimes the same product with different code
Eg: Ladies Jeans - PRN - 350
Ladies Jeans - PAR - 395

#### Shaji Panchali

##### New Member
Could you please explain the logic? Looks quite obscure how for example PRN becomes 350 ?
Really apologise on the error in the question itself.. I'm working on couple of codes and hence the confusion in writing the codes and their value.

》If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0

Then, Actual result would be as follows :

》In cell E2 if i enter "SRK", in cell F2 i should get "350" or
》In cell E3 if i enter "BR", in F3 i should get "75" or
》In cell E4 if i enter "MY", in F4 i should get "12"

#### bosco_yip

##### Excel Ninja
》If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0
Then, Actual result would be as follows :
》In cell E2 if i enter "SRK", in cell F2 i should get "350" or
》In cell E3 if i enter "BR", in F3 i should get "75" or
》In cell E4 if i enter "MY", in F4 i should get "12"
Perhaps,

In F2, formula copied down :

=IF(LEFT(E2)="K",0,"")&SUMPRODUCT((FIND(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),"KMYSOREBAN")-1)*10^(LEN(E2)-ROW(INDIRECT("1:"&LEN(E2)))))

Regards
Bosco

Last edited:

#### Haz

##### Active Member
A slightly shorter formula, but with the same principle
=INT(NPV(-0.9,,INDEX(SEARCH(MID(E2,LEN(E2)-ROW(\$A\$1:INDEX(A:A,LEN(E2)))+1,1),"KMYSOREBAN")-1,))%)