Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |