Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove characters from string | Excel Programming | |||
Remove characters from string | Excel Programming | |||
Remove characters from string | Excel Programming | |||
Remove all characters following the first character in a string | Excel Discussion (Misc queries) | |||
Remove characters from a text string using a formula | Excel Discussion (Misc queries) |