Extract and return only alpha, numeric and also specified characters from a parameter str

[brucemc777]

Happy New Year Everyone!

I am monkeying around with some code written by Peter Albert.

I wish to extend the ability to not only extract specified categories (alpha, numeric, and alpha-numeric)(This is presently accomplished by passing the optional parameter "strLimit"), but also to be able to be fed additional special characters in any given call.

The first part was easy by simply implementing an additional parameter and If/End structure such as:

Please Login or Register  to view this content.

In an effort to convert it to the feed of specific characters, my first step was to try to feed a string variable to the Case, first based upon the major category I wanted to extract (strLimit). Since Case takes literal parameters, I attempted to use Evaluate, but that failed with a "Type Mismatch" error:

Please Login or Register  to view this content.

The train of thought was that if I can feed a string that I can build upon to CASE, then I could concatenate in to strCase the additional ASCII codes which I would build off of a third parameter sent to this function, which would be parsed into codes (strExceptions).

Is there a way that my attack can be accomplished, or need I work on another direction? I do have an ugly solution that calls for an If/End control and provides for a different literal string for every combination of Major Categories plus other characters I might wish to include (the start of which you see in the first example above), but as the variations grew I wanted to make the code adapt instead of adding another "ElseIf" each time I wanted to address yet another character combination!

I regret that my ability to conceive of variations to experiment with are limited by my understanding when faced with the Select Case structure taking literal parameters and not variables. I have researched the internet for a bit but could not yet find a solution.

Thank-you for considering my problem!

[LJMetzger]

Hi Bruce,

You may want to try using the 'like' command. For more information see https://msdn.microsoft.com/en-us/lib.../gg251796.aspx

Regular expressions are more powerful than the 'like' command, but can become complicated to use very quickly. For a good tutorial see http://analystcave.com/excel-regex-tutorial/

The following example code using 'like' may help you out:

Please Login or Register  to view this content.

Lewis

[brucemc777]

Interesting! From my initial testing I find that the Like statement can be fed a string variable in the form of

Like "[" & strTest & "]"

So in the structure you presented above, if all works well, I should be able to achieve what I wish.

Thank-you very much!

[brucemc777]

In that I am always revising what I write I suppose there are some modifications that can be made to the following to make it more efficient, but I wanted to post this in the event anyone else might be looking for a similar solution-

Many thanks to Peter Albert and LJMetzger!

Please Login or Register  to view this content.

[LJMetzger]

Hi Bruce,

Thanks for the kind words and the rep points. Since you are doing the calculations in a loop and the VBA Interpreter (Compiler) does not optimize, the following change will make the routine more efficient. The time savings will not be noticeable unless you call this routine often using large data strings:

Please Login or Register  to view this content.

Lewis

[brucemc777]

I see; so the program does not have to re-evaluate the concatenation over and over. Thanks!

Also I was wondering about something from the start - you used

Const strSource = "abc123456def7890xyz"

I would have written something like:
Dim strSource as String
strSource = "abc123456def7890xyz"

Can you explain the difference?

[LJMetzger]

In this case they are equivalent, and it took less keystrokes.

A constant (Const) is a variable whose value can not be changed. Down at the bits and bytes level (which I used to care about in the old days when where an item was stored really mattered), they are also probably stored by VBA in different places.

The discussion about 'Global Variables' near the end of the following link may help with the the following discussion: http://www.functionx.com/vbaexcel/Lesson03.htm

Example code:

Please Login or Register  to view this content.

When I need a variable whose value will not change, I declare it as a constant. My personal convention is to CAPITALIZE the last few characters in a constant name, so I know it is a constant just by looking at the variable name. A compiler error will occur if you try to assign a value to a constant. Regular variables (and constants) have 3 basic types of scope (availability):

a. Global scope accessible by all code in any module in the Workbook. The keyword Public is used, and the constant is declared before the first routine and after 'Option Explicit' (if 'Option Explicit' is used - highly recommended).

b. Global scope accessible by all code in any routine in the same module. The keyword Private is used, and the constant is declared before the first routine and after 'Option Explicit'.

c. Local scope accessible only by code in the same routine.

It is very important to note that the same 'Constant Name' can be declared in more than one place and can have different values such as xPi above. Lower level variable (and constant) declarations ('Local Scope') take precedence over 'Module Scope' (Private), and 'File Scope' (Public).

'Module Scope' (Private) takes precedence over 'File Scope' (Public). When I have 'Public' variables (and constants) in a project, I usually put them all in their own code Module which I call ModConstantsAndGlobals.

I hope this helps.

Lewis

[brucemc777]

Thank-You!!!