Remove characters from string

[Ken McLennan]

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.

 

[Niek Otten]

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/templates/CT101527321033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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

 

[Helmut Weber]

Hi Ken,

I think you are on the wrong track with
trying to convert something wrong into something right.

The complications would be endless.

You should try to prevent something wrong inserted
in a cell beforehand.

If you can't use a datetimepicker-control or whatever,
I think, you better set up two dropdown-listboxes,
one for the hour, the other for the minute,
or try some other method for selecting from
predefined values.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ron Rosenfeld]

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.

Here's a routine, using regular expressions, that will extract the Time and the
a or the p.

It works on your examples.

However, if there were a number preceding the time that was not a time, it
would have to be modified.

It will return the existing time string and, if present in the original, an "a"
or a "p".

If some of the entries in your data differ significantly from what you've
posted, small changes in the "regex" may be required.

------------------------------------------
Option Explicit

Sub TestTimeParser()
Dim T(4) As String
Dim i As Long
T(0) = "6"
T(1) = "6A"
T(2) = "11:41 on call"
T(3) = "6:00 am On Call"
T(4) = "6:00pm On Call"

Dim objRe As Object
Dim colMatches As Object
Const Pattern As String = "[0-9:]+(\s?[ap])?"

Set objRe = CreateObject("vbscript.regexp")
objRe.Global = True
objRe.Pattern = Pattern
objRe.ignorecase = True

For i = 0 To UBound(T)

If objRe.test(T(i)) = True Then
Set colMatches = objRe.Execute(T(i))
Debug.Print Replace(colMatches(0), " ", "")
End If

Next i

End Sub

 

[Zack Barresse]

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

 

[Tom Ogilvy]

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.

 

[Ken McLennan]

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/templates/CT101527321033.aspx

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

 

[Ken McLennan]

G'day there Helmut,

I think you are on the wrong track with
trying to convert something wrong into something right.

While you're quite correct with the perspective on conversion,
that aspect is out of my control.

The complications would be endless.

You should try to prevent something wrong inserted
in a cell beforehand.

The problem is that I have no access to the original forms.
They're simply an electronic copy of the old handwritten form that used
to be used. I work for a government dept that sees MS Office as a
glorified typewriter. Automation of documents is just about non existent

If you can't use a datetimepicker-control or whatever,
I think, you better set up two dropdown-listboxes,
one for the hour, the other for the minute,
or try some other method for selecting from
predefined values.

That's for my next project =)

For the moment, I can not change the input forms. They are totally
beyond my control. I need to work with the data I get regardless of how
crappy it may be. That might be why the supervisor of the section I'm
writing this for has been trying to get someone to take on the project
for about 5 years. One developer even tried a dedicated database without
success.

Maybe because I'm only a hobbyist, not a developer/programmer, I
was too dim to realise that it couldn't be done <g>.

In a serious vein I've already started working on the input side
of things. The form that's used for roster entry does have a few macros,
but they're only keystroke shortcuts that were included when the dept
introduced the first electronic form on our early Apple 603's. The form
hasn't changed in form or presentation since then.

I've already presented a prototype and it was warmly received.
However I wasn't encouraged when I asked the head of the section that
writes our electronic documents for a copy of the workbook with the
password removed so I could access those modules and no one there knew
what I was talking about. They thought I was talking about the worksheet
password (on a sheet that wasn't protected) and weren't aware that there
was a password on the module. That was 3-4 years ago and the dept is
still using the same worksheet and I'm still waiting for my unlocked
copy. I may be wrong, but since every electronic form we get is based on
MSWord, with tables for anything with rows & columns - including
financial calculations, I get the feeling that there's not a lot of
Excel expertise available.

 

[Ken McLennan]

G'day there Ron,

Here's a routine, using regular expressions, that will extract the Time and the
a or the p.

It works on your examples.

Then it will more than likely work on the real data, too.

However, if there were a number preceding the time that was not a time, it
would have to be modified.

I can do a bit of 'pre-cleaning' to, hopefully, alleviate such a
thing.

It will return the existing time string and, if present in the original, an "a"
or a "p".

That's what I'm after. If there's no "a" or "p" it's not a problem
as long as the string on the other side of the hyphen has one. I've
already written code that calculates the correct letter from what's on
the other side. It's a bit clumsy, but it works so I'm not gonna touch

If some of the entries in your data differ significantly from what you've
posted, small changes in the "regex" may be required.

Nodnodnod. I'll give it a try tonight. The first half (before the
hyphen) is not a problem as there are no code letters introduced
adjacent to the hyphen (thank the Gods for small mercies). In one small
aspect over which I have some control, I've requested (don't have
authority to TELL anyone) that a space be left after the shift ending
time to differentiate between times and any extra characters. I can't
enforce it but I did ask politely =).

Set objRe = CreateObject("vbscript.regexp")

Is that a standard library? I'll have to check after my days off
to see if it's installed. If not then I'm out of luck (dammit!!) Still,
I'll just have to try it and see.

Thanks very much for your help. It's greatly appreciated.

 

[Ken McLennan]

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!

 

[Niek Otten]

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


| >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
|
| | > 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/templates/CT101527321033.aspx
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | > | 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
| >
| >
|
|

 

[Helmut Weber]

Hi Ken,

i had put up this one before,
but didn't dare to post it.

Though perfection is not possible,
it might help you.

Sub Test()
Dim ba As Boolean ' "a" was found
Dim bp As Boolean ' "p" was found
Dim strC As String
Dim str1 As String
Dim str2 As String
Dim lngC As Long
str1 = "6:00am On Call"
For lngC = 1 To Len(str1)
strC = (Mid(str1, lngC, 1))
Select Case strC
Case "1" To "9": str2 = str2 & strC
Case "a"
If ba = False Then str2 = str2 & strC
ba = True
Case "p"
If bp = False Then str2 = str2 & strC
bp = True
End Select
Next
MsgBox str2
End Sub

G'day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ken McLennan]

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.

 

[Niek Otten]

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


| 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

 

[Ken McLennan]

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.

 

[Ron Rosenfeld]

Is that a standard library? I'll have to check after my days off
to see if it's installed. If not then I'm out of luck (dammit!!) Still,
I'll just have to try it and see.

Yes, it is.

You could also set a reference (Tools/References) to Microsoft VBScript Regular
Expressions 5.5 and use this equivalent routine, which should run faster.

=============================
Option Explicit

Sub ParseNames()
Const T As String = "Name1 Name2 Name3 Name4"
Dim i As Long

Dim objRe As RegExp
Dim colMatches As MatchCollection

Const Pattern As String = "\w+"

Set objRe = New RegExp
objRe.Pattern = Pattern
objRe.Global = True

If objRe.test(T) = True Then
Set colMatches = objRe.Execute(T)

For i = 0 To colMatches.Count - 1
Debug.Print colMatches(i)
Next i

End If
End Sub

==========================
--ron

 

[Ron Rosenfeld]

I can do a bit of 'pre-cleaning' to, hopefully, alleviate such a
thing.

If you can present the variability in the original data, it may be easy to
modify the regex "Pattern"


--ron

 

[Tom Ogilvy]

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

 

[Ron Rosenfeld]

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

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

 

[Ken McLennan]

G'day there Tom,

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

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