Remove characters from string
 

Hi Ken,

Helmut is on the right track. Work on prevention rather than clean-up. It
will make your life much easier. Depending on how this information is sent
to you, if in Excel, you have options such as Data Validation, UserForms,
sheet controls or even worksheet event code.

HTH
Zack Barresse, aka firefytr



"Ken McLennan" wrote in message
...
G'day there One and All,

This latest query of mine will, I hope, find a more elegant method
than the one I'm now using.

My code processes a number of strings which relate to shift
starting times. They're in varied formats and my code tries to reduce
them to the same format for ease of processing. For instance, I might
have a time entered by my users as "6:00", "6am", or just plain "6" (I
need an appropriate shift end time to determine whether the latter is
"am" or "pm").

I have things working fairly well, and as part of the parsing of
the string I remove extraneous characters. What I want to do is to
remove every letter except an "a" or a "p" so that "6pm" becomes "6p",
and "6:00am On Call" becomes "6a". (In case you're wondering, before I
can do anything useful, the time string is attatched to a date and
converted to a standard date/time format as data type "date", not data
type "string").

I currently process it by referring each character in the string,
in turn, to this list:

"bcdefghijklmnoqrstuvwxyz"

with "instr(str_TESTSTR)" and removing anything that matches, but
this seems quite cumbersome and time consuming.

I have no clue when it comes to reg expressions, and don't even
know whether any such thing would be a useful technique in this
instance.

Is there some other way to do it, that my pedestrian grey matter
hasn't comprehended? Is there some way to stuff the above 24 char string
into a "replace" function, maybe?

My code will (eventually) be deployed across the state and there
is no possibility of having anything installed on the target machines so
extra functions via add-ins are out. I can only use what I manage to
stuff into a module via the VBE.

Surely there's a better way to do it than what I've come up with!

Thanks for listening.
--
See ya,
Ken McLennan
Qld, Australia

Remove characters from string
 

G'day there Zack,

Helmut is on the right track.

He usually is =)

Work on prevention rather than clean-up.

I would dearly love to, but that's out of my hands at the moment
(I've just replied to another message in this thread re that matter).

It will make your life much easier.

I certainly shan't argue that with you. I fully agree. The problem
is that they are two separate entities.

The first entity is a worksheet filled with strings describing
shifts. It's designed completely as an electronic copy of a paper form
with almost no automation - that some clients don't even bother to use.

The second, my current project, uses those forms as raw data for
the construction of a list of selected employees with associated data
(based on shift times - that is "Who's working at 17:00 on Saturday the
25th?") that can be sorted, filtered, printed, and manipulated.

So, you're exactly right but I'm stuck with what I've got until I
can get to my next project which addresses the problems with the first
entity.

Depending on how this information is sent
to you, if in Excel, you have options such as Data Validation, UserForms,
sheet controls or even worksheet event code.

No. Excel has those options, but I don't. Not yet, anyway!!

If I worked for someone other than a government department, the
whole thing would probably have been automated years ago!

Thanks for your advice. I *do* appreciate it!

--
See ya,
Ken McLennan
Qld, Australia