Hi Ken,

You might find Laurent Longré's functions for Regular Expressions useful; they are well explained on his site

http://office.microsoft.com/en-us/te...527321033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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

so extra functions via add-ins are out.

and your link took me to the templates page at microsoft office. Nothing
there expaining regular expressions unless you meant I should search for it.

--
Regards,
Tom Ogilvy

"Niek Otten" wrote in message
...
Hi Ken,

You might find Laurent Longré's functions for Regular Expressions useful;
they are well explained on his site

http://office.microsoft.com/en-us/te...527321033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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

G'day there Niek,

You might find Laurent Longré's functions for Regular Expressions useful; they are well explained on his site

http://office.microsoft.com/en-us/te...527321033.aspx

You're more than likely correct, however at the moment the Cricket
is on TV so it will have to wait =).


--
See ya,
Ken McLennan
Qld, Australia

It takes some browsing to find it on Laurent's site, but this is even worse! Obviously a mistake. I meant

http://xcell05.free.fr/

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Tom Ogilvy" wrote in message ...
| so extra functions via add-ins are out.
|
| and your link took me to the templates page at microsoft office. Nothing
| there expaining regular expressions unless you meant I should search for it.
|
| --
| Regards,
| Tom Ogilvy
|
| "Niek Otten" wrote in message
| ...
| Hi Ken,
|
| You might find Laurent Longré's functions for Regular Expressions useful;
| they are well explained on his site
|
| http://office.microsoft.com/en-us/te...527321033.aspx
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "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
|
|
|
|

G'day there Niek,

It takes some browsing to find it on Laurent's site, but this is even worse! Obviously a mistake. I meant

http://xcell05.free.fr/

I've browsed Laurent's site, and unfortunately didn't find much
that I could read. I found only a single page in English and it didn't
refer to Reg Expressions.

Thanks anyway, though. I appreciate any helping hand that's
offered.

--
See ya,
Ken McLennan
Qld, Australia

Hi Ken,

In that English page, look at the list of functions in MOREFUNC; there are several REGEX.... functions.
Pick any of these and click the link to Regular expressions in the text, to get an explanation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Ken McLennan" wrote in message ...
| G'day there Niek,
|
| It takes some browsing to find it on Laurent's site, but this is even worse! Obviously a mistake. I meant
|
| http://xcell05.free.fr/
|
| I've browsed Laurent's site, and unfortunately didn't find much
| that I could read. I found only a single page in English and it didn't
| refer to Reg Expressions.
|
| Thanks anyway, though. I appreciate any helping hand that's
| offered.
|
| --
| See ya,
| Ken McLennan
| Qld, Australia

G'day there Niek,

In that English page, look at the list of functions in MOREFUNC; there are several REGEX.... functions.
Pick any of these and click the link to Regular expressions in the text, to get an explanation

You're quite right. Perhaps I should have looked a bit closer the
first time =).

I can even understand some of the explanations, so I may just be
off to the world of regexes. Not at the moment though. It's past my bed
time so I'm off to sleep. Regexes can wait until tomorrow.

Thanks for taking the time to let me know where to look. It's very
kind of you. Thanks.

--
See ya,
Ken McLennan
Qld, Australia

Since Laurent's solution is an addin, then you were joking about?:

so extra functions via add-ins are out.

--
Regards,
Tom Ogilvy

"Ken McLennan" wrote in message
...
G'day there Niek,

In that English page, look at the list of functions in MOREFUNC; there
are several REGEX.... functions.
Pick any of these and click the link to Regular expressions in the text,
to get an explanation

You're quite right. Perhaps I should have looked a bit closer the
first time =).

I can even understand some of the explanations, so I may just be
off to the world of regexes. Not at the moment though. It's past my bed
time so I'm off to sleep. Regexes can wait until tomorrow.

Thanks for taking the time to let me know where to look. It's very
kind of you. Thanks.

--
See ya,
Ken McLennan
Qld, Australia

On Sun, 28 Jan 2007 14:27:01 -0500, "Tom Ogilvy" wrote:

Since Laurent's solution is an addin, then you were joking about?:

so extra functions via add-ins are out.


Actually, Longre's add-in can be easily embedded in the workbook, and
distributed with it. There is a menu option to do so.
--ron

G'day there Tom,

Since Laurent's solution is an addin, then you were joking about?:

so extra functions via add-ins are out.

No, not at all. My forays into regexes are intended for my own
benefit and for use in other projects. I'm afraid the "No Addins" is
true.

See ya
Ken


--
See ya,
Ken McLennan
Qld, Australia

A functionality added after the last time I downloaded it - thanks for the
update.

--
Regards,
Tom Ogilvy


"Ron Rosenfeld" wrote in message
...
On Sun, 28 Jan 2007 14:27:01 -0500, "Tom Ogilvy" wrote:

Since Laurent's solution is an addin, then you were joking about?:

so extra functions via add-ins are out.


Actually, Longre's add-in can be easily embedded in the workbook, and
distributed with it. There is a menu option to do so.
--ron

G'day there Ron,

Actually, Longre's add-in can be easily embedded in the workbook, and
distributed with it. There is a menu option to do so.

Hmmm... If that's the case, then I guess I need to read up
on add-ins.

--
See ya,
Ken McLennan
Qld, Australia

On Mon, 29 Jan 2007 03:45:55 GMT, Ken McLennan
wrote:

G'day there Ron,

Actually, Longre's add-in can be easily embedded in the workbook, and
distributed with it. There is a menu option to do so.

Hmmm... If that's the case, then I guess I need to read up
on add-ins.

In this case, using regular expressions through VBA, it may be more efficient
to use the VBA method I posted or, if you want to use worksheet functions, to
rewrite it as a function.


If you do decide to use Longre's stuff as a function, you could use the same
pattern, and the formula would be:


=REGEX.MID(A1,"[0-9:]+(\s?[ap])?")


--ron