Changing individual chars within a cell

[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 EditReplace is not sufficient!

 

[Rob van Gelder]

Sub test()
Dim rng As Range, strCode As String, str As String, i As Long

For Each rng In Range("A1:A5")
strCode = ""
For i = 1 To Len(rng.Value)
Select Case LCase(Mid(rng.Value, i, 1))
Case "h": str = 1
Case "e": str = 2
Case "l": str = 3
Case "o": str = 4
End Select
strCode = strCode & str
Next
rng.Value = strCode
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel

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 EditReplace is not

sufficient!

 

[Guest]

Many Thanks - that works a treat

Sub test()
Dim rng As Range, strCode As String, str As String, i As Long

For Each rng In Range("A1:A5")
strCode = ""
For i = 1 To Len(rng.Value)
Select Case LCase(Mid(rng.Value, i, 1))
Case "h": str = 1
Case "e": str = 2
Case "l": str = 3
Case "o": str = 4
End Select
strCode = strCode & str
Next
rng.Value = strCode
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel



what's in the cell to some sort of code.
code, for example H is 1, e is 2, l is 3 and 0 is 4, so Hello would be 12334
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??
sufficient!