Use Excel as a "code" translator... 1

[jsbaby80]

Is there a way in Excel to enter a "code" into a cell ie VXX.XX and have it translated to what it means ie 600.00? This code will have been preset by me... Sorry I don't know how to explain it better. The code is as follows
V E R N S M I T H X
1 2 3 4 5 6 7 8 9 0

 

[xlbo]

Needs code / a UDF
set up a lookup table
Letters in the 1st column, numbers in the right - call it "Decode"

then, using the worksheet change event (feel free to ask if this means nothing to you)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
mStr = Target.Text
For i = 1 To Len(mStr)
testChar = Mid(mStr, i, 1)
If testChar = "." Then
conVal = "."
Else
conVal = WorksheetFunction.VLookup(testChar, [Decode], 2, False)
End If
TempStore = TempStore & conVal
Next i
Target.Value = TempStore
Application.EnableEvents = True
End Sub



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Zathras]

Since this isn't the VBA forum, perhaps you would prefer a non-VBA solution:

If your code string is in A1, then put this in B1 and C1 to see the result in C1:
[blue]

A1: VERTNXH.SMI
B1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"V",1),"E",2),"R",3),"N",4),"S",5)
C1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"M",6),"I",7),"T",8),"H",9),"X",0)

[/color]
You should see [blue] 1238409.567 [/color]

Unfortunately you can't put it all in one formula because of Excel's nesting limit of 7. But of course, you can put the intermediate formula in a far-off cell (or in a hidden column.)

 

[jsbaby80]

Zathras, Thank you!!! Just what I was looking for!!!

 

[xlbo]

Nice Z - very nice - have a star

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

Just in case you could use a solution that put the numbers into single cells:-

With your data in cell A1, create your list of values in a table say A5:B30. Now in any cell, type = and then select the area A5:B30, then hit F2 and F9 and enter. Copy the 'contents' of the cell and then do Insert / Name / Define (Call it MyList) and paste in the value from the cell, which will look something like this:-

={"a",7;"b",11;"c",23;"d",18;"e",4;"f",26;"g",5;"h",20;"i",15;"j",12;"k",25;"l",21;"m",2;"n",3;"o",24;"p",1;"q",10;"r",8;"s",22;"t",6;"u",14;"v",9;"w",13;"x",17;"y",19;"z",16}

You can now delete your table in A5:B30

Now select as many cells as you think will need to use - You can select far more than you need to cover large words, eg C1:BZ1 and then in the formula bar, paste in the following formula:-

=IF(ISERROR(VLOOKUP(MID(A1,COLUMN(INDIRECT("A:BX&quot),1),MyList,2,0)),"",VLOOKUP(MID(A1,COLUMN(INDIRECT("A:BX&quot),1),MyList,2,0))

Now hit CTRL+SHIFT+ENTER to array enter it.

Format your column widths as 2 or something like that.

The A:BX simply maps to the same number of cells as the C:BZ range and can be changed as you prefer.

Regards
Ken..................



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jsbaby80]

I tried to reverse this, and everytime I change the R around it gives me a formula error...