Macro
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. |
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. |
All times are GMT +1. The time now is 03:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com