Macro
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
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
"Odin" wrote:
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.
|