Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing individual chars within a cell
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing individual chars within a cell
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 "cdb" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing individual chars within a cell
Many Thanks - that works a treat
"Rob van Gelder" wrote: 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 "cdb" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. | Excel Worksheet Functions | |||
limit # of chars in a cell | Excel Discussion (Misc queries) | |||
Mass Changing whole sheets individual cell references | Excel Discussion (Misc queries) | |||
Copy sheets with more than 255 chars in a cell? | Excel Worksheet Functions | |||
1160 Max Chars per cell ? Workaround ? | Excel Programming |