Special Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to generate price list with this

G = 1
O = 2
L = 3
D = 4
E = 5
N =6
C= 7
I = 8
T = 9
Y = 0

When i enter 100 it should return GYY

HOW IT IS POSSIBLE

Thanks and regards

RRS
 
one way might be to set up a table and use this formula.
1 G 100
2 O
3 L
4 D
5 E
6 N
7 C
8 I
9 T
0 Y

=VLOOKUP(VALUE(LEFT(F1,1)),D1:E10,2,0)&VLOOKUP(VALUE(MID(F1,2,1)),D1:E10,2,0)&IF(LEN(f1)>2,VLOOKUP(VALUE(MID(F1,3,1)),D1:E10,2,0),"")
 
hI Rao, I received this code from Aidan,

On the spreadsheet, press ALT+F11 open a new module copy and past this code:

Function GetDigit(Digit As String)
Dim CheckDigit As String
Dim looper
For looper = 1 To Len(Digit)
CheckDigit = Mid(Digit, looper, 1)
Select Case Val(CheckDigit)
Case 1: GetDigit = GetDigit & "G"
Case 2: GetDigit = GetDigit & "O"
Case 3: GetDigit = GetDigit & "L"
Case 4: GetDigit = GetDigit & "D"
Case 5: GetDigit = GetDigit & "E"
Case 6: GetDigit = GetDigit & "N"
Case 7: GetDigit = GetDigit & "C"
Case 8: GetDigit = GetDigit & "I"
Case 9: GetDigit = GetDigit & "T"
Case 0: GetDigit = GetDigit & "Y"
Case Else: GetDigit = ""
End Select
Next
End Function

so, imagine you have the 100 on the cell A1 the formula is:

=getdigit(a1)

regards










"Rao Ratan Singh" escreveu:
 
Assuming your Price is in cell A1, put this formula in cell B1 and copy it
over to cell K1...........

=IF(LEN($A1)>=COLUMN(A1),LOOKUP(MID($A1,COLUMN(A1),1)*1,{0,1,2,3,4,5,6,7,8,9},{"Y","G","O","L","D","E","N","C","I","T"}),"")

Then, if you wish the new "Alpha-code" to all be in one cell, you can put
this formula in cell L1

=CONCATENATE(B1,C1,D1,E1,F1,G1,H1,I1,J1,K1)

All formulas above can be copied down as needed.........

hth
Vaya con Dios,
Chuck, CABGx3
 
Back
Top