Macro

[Odin]

I have written the Macro below in Excel that returns the generic # for
number and @ for a letter e.g. 'abc123' would be '@@@###':

Public Function Kabi_it(thing)
Dim work1
For i = 1 To Len(thing)
If (Asc(Mid(thing, i, 1)) > 96) And (Asc(Mid(thing, i, 1)) < 123)
Then
work1 = work1 & "@"
ElseIf (Asc(Mid(thing, i, 1)) > 64) And (Asc(Mid(thing, i, 1)) <
91) Then
work1 = work1 & "@"
Else
work1 = work1 & "#"
End If
Next
Kabi_it = work1

End Function

I have two questions:
1) Is there a simpler elegant way of acheiving a similar result; and
2) I want to narrow this down so that if the first a string contains
'O', 'D' or '0', these are ignored and left the same e.g. 'abcd1023o'
would be '@@@d#0##o'.

Thank you.

 

[Guest]

Hi Odin:

Try this, note I moved the test to check numbers only except for the special
characters.

You do not consider none letters or digits I have grouped them with letters.


Option Explicit

Public Function Kabi_it(ByVal item As String) As String

Dim work1 As String
Dim i As Long
Dim c As Byte
For i = 1 To Len(item)
c = Asc((Mid(item, i, 1)))
Select Case c
Case 68, 100, 79, 111, 48
work1 = work1 & Chr(c)
Case 48 To 57 ' 0..9
work1 = work1 & "#"
Case Else ' other characters
work1 = work1 & "@"
End Select
Next
Kabi_it = work1

End Function