Changing individual characters in a cell to a different character

[Guest]

I am trying to write a bit of code that will look at a cell and covert what's in the cell to some sort of code.

e.g.

Hello Goodbye Yes No
The above words are in different cells, and need converting to a different code, for example H is 1, e is 2, l is 3 and 0 is 4, so Hello would be 12334

The entries to the cells will be different, so I can't just do a select case and have Hello as one of them. Is there any way of doing a select case type statement, but for it to look at individual characters in a cell and cycle through until it's finished and then move onto the next cell??

Or is there an easier way of doing this?? A simple Edit>Replace is not sufficient!

 

[Bernie Deitrick]

Would Goodbye be 1223456? Or would it depend on the values already used in
Hello?

HTH,
Bernie
MS Excel MVP

I am trying to write a bit of code that will look at a cell and covert

what's in the cell to some sort of code.

e.g.

Hello Goodbye Yes No
The above words are in different cells, and need converting to a

different code, for example H is 1, e is 2, l is 3 and 0 is 4, so Hello
would be 12334

The entries to the cells will be different, so I can't just do a select

case and have Hello as one of them. Is there any way of doing a select case
type statement, but for it to look at individual characters in a cell and
cycle through until it's finished and then move onto the next cell??

Or is there an easier way of doing this?? A simple Edit>Replace is not

sufficient!

 

[Bernie Deitrick]

How do you determine the substitution code? Is it 'preset' or 'first come,
first substituted'? How would you handle more than 9 substitutions - two
digit, or other characters?

HTH,
Bernie
MS Excel MVP

There would be a code for each letter in the alphabet that would be

constant throughout the spreadsheet. So using the same example, the e in
Goodbye would be 2 (as in the e in Hello)

 

[Guest]

The substitution is a preset list of constants. It is a 2 digit code (both numeric and alpha-numeric. I just used the 1-2-3-4 example for ease of explanation.

 

[Bernie Deitrick]

cdb,

Select your cells with "hello" etc, and run code like this: make sure you
copy the option statements as well.

Option Base 1
Option Explicit
Sub TryNow()
Dim myCode As Variant
Dim myCell As Range
Dim i As Integer
Dim myStr As String

myCode = Array("1", "2", "3", "4", "5", "6", _
"7", "8", "9", "A", "B", "C", _
"D", "E", "F", "G", "H", "I", _
"J", "K", "L", "M", "M", "O", _
"P", "Q")
For Each myCell In Selection
If myCell.Value <> "" Then
myStr = ""
For i = 1 To Len(myCell.Value)
myStr = myStr & myCode(Asc(UCase(Mid(myCell.Value, i, 1))) - 64)
Next i
myCell.Value = "'" & myStr
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

The substitution is a preset list of constants. It is a 2 digit code (both

numeric and alpha-numeric. I just used the 1-2-3-4 example for ease of
explanation.