How do I code using a cell?

Closed
TSCGCobra043 Posts 5 Registration date Monday December 14, 2015 Status Member Last seen December 16, 2015 - Dec 14, 2015 at 04:48 PM
 Blocked Profile - Dec 16, 2015 at 06:20 PM
Hello,

I want to make a code that takes "50,41,62,62,60" and change each number to a letter so it's says something like a phrase such as "hello" how would I do that?
thank you

1 response

Blocked Profile
Dec 14, 2015 at 05:38 PM
Please understand, we are volunteers who help when stuck!

It sounds like you have a homework problem? If not, I encourage you to look up and read about VBA-Visual Basic for Applications. It is a Microsoft platform and is widely excepted!
-1
TSCGCobra043 Posts 5 Registration date Monday December 14, 2015 Status Member Last seen December 16, 2015
Dec 15, 2015 at 11:35 AM
no I'm trying to make a conversion calculator in two cells. I have all the numbers and letters already set but I can't figure out how to make the equation read each letter/number in the cell and convert it
0
You cannot do this in an equation.
It requires a custom function to do this as loops are required to pull out the numbers from cell to lookup the associated letter add it to a string, skip the comma (or delimiter) and then move onto the next number in the cell. Repeat until there are no more numbers. Display the string in the cell.

It will help if you posted the cell ranges of your letter/number cells, or even a screenshot or example spreadsheet
0
Blocked Profile
Dec 15, 2015 at 04:59 PM
@Thank you for your fine explanation.
0
Here is a function that does what you need.

In a module put the following code:
Function ConvertCode(code As Range) As String

Start = 1
While Start < Len(code)

    Pos = InStr(Start, code, ",", vbTextCompare)
    If Pos = 0 Then Pos = Len(code) + 1
    StrValue = Mid(code, Start, Pos - Start)
    Start = Pos + 1
    Set rng1 = Range("A1:A31").Find(StrValue, , xlValues, xlWhole)
    StrValue = rng1.Offset(0, 1)
    ConvertCode = ConvertCode & StrValue
    
Wend

End Function



This assumes the following (as I do not know the layout you are using):
1.Column A contains the number.
2.Column B contains the character

The Range contains 31 rows as the list of characters includes other non-alphabetic characters such as Space, #, $, as these may be useful. Add other characters as you need but adjust the range accordingly.

To use the function, lets say the code you want to convert is in cell D2, in another cell, put:
=ConvertCode(D2)
0
TSCGCobra043 Posts 5 Registration date Monday December 14, 2015 Status Member Last seen December 16, 2015 > RayH
Dec 16, 2015 at 10:42 AM
thank you
0