Combined Coded letters in one cell

[Shaji Panchali]

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)

 

[pecoflyer]

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

 

[Shaji Panchali]

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]

Shaji Panchali

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

 

[salim hasan]

Use This Macro
File Attached

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

 

[pecoflyer]

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

 

[Shaji Panchali]

Shaji Panchali

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

Thanks for your reply.. Deepak
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]

Use This Macro
File Attached

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

 

[salim hasan]

Maybe like this

 

[NPOKALA]

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]

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]

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]

》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

 

[Haz]

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,))%)