Get First Letter of each word
 

Hi all,

I am trying to do get the first letter of each word to create it's
individual Identifier with code to use further on in my script. Examples
of the string below and then the Identifier.

Shatterprufe - Rear Window = SRW
Rehau - W/Shield Cvr & Package = RWCP
Shatterprufe - Side Glass = SSG
Rehau - Frt. Air Duct = RFAD

Thank you in advance for any help....


Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Get First Letter of each word
 

On Wed, 20 Feb 2008 07:02:23 -0800, Les Stout wrote:

Hi all,

I am trying to do get the first letter of each word to create it's
individual Identifier with code to use further on in my script. Examples
of the string below and then the Identifier.

Shatterprufe - Rear Window = SRW
Rehau - W/Shield Cvr & Package = RWCP
Shatterprufe - Side Glass = SSG
Rehau - Frt. Air Duct = RFAD

Thank you in advance for any help....


Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***


Here are two methods that return a string consisting of the first letter in the
string and every subsequent letter that follows a <space.

One version uses regular expressions; the other takes advantage of VBA's Split
function. The Split function appeared, I believe, in XL2002.

========================================
Option Explicit
Function ID(str As String) As String
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.ignorecase = True
re.Global = True
re.Pattern = "(^|\s)([A-Z])"
re.ignorecase = True
Set mc = re.Execute(str)
For Each m In mc
ID = ID & UCase(m.submatches(1))
Next m
End Function
'-------------------------------------------
Function IDa(str As String) As String
Dim sTemp() As String
Dim i As Long
sTemp = Split(str)

For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i

End Function
===========================================
--ron

Get First Letter of each word
 

Just a heads up -

The Split function appeared, I believe, in XL2002.

Split and a number of other string functions first appeared in XL2000,
alternatives required for XL97

Regards,
Peter T

Get First Letter of each word
 

Thanks so much Ron..

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Get First Letter of each word
 

Hi Ron, I am batteling a bit with the way to call this function, could
you give me an example please...

Thanks

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Get First Letter of each word
 

Hi Ron, I am batteling a bit with the way to call this function,
could you give me an example please...

Let's assume in **your** code, you have two variables named OriginalString
and FirstLettersOfEachWord, then...

' Not sure how your program is processing the orignal
' strings, but this line assumes they are, or can be, stored
' in a variable
OriginalString = "Shatterprufe - Rear Window"
' To use the ID function (which gives you the first letters of
' each word), simple pass the variable containing the original
' string of text into the function and use its return value
FirstLettersOfEachWord = ID(OriginalString)
' Show that the function worked
MsgBox FirstLettersOfEachWord

Rick

Get First Letter of each word
 

Function IDa(str As String) As String
Dim sTemp() As String
Dim i As Long
sTemp = Split(str)

For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i

End Function

Just a point of information... using the UCase function in the If-Then
statement above (to parse upper and lower case letters) will work as shown,
but this should be more efficient:

If sTemp(i) Like "[a-zA-Z]*" Then

Rick

Get First Letter of each word
 

On Wed, 20 Feb 2008 12:04:03 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Function IDa(str As String) As String
Dim sTemp() As String
Dim i As Long
sTemp = Split(str)

For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i

End Function

Just a point of information... using the UCase function in the If-Then
statement above (to parse upper and lower case letters) will work as shown,
but this should be more efficient:

If sTemp(i) Like "[a-zA-Z]*" Then

Rick

Well, I wondered about checking that. So I ran a routine identical except for
the comparison statement (see code below).

For 10,000 repetitions, the UCase variation took 0.4375 seconds, whereas the
[a-zA-Z] variant took 1.125 seconds. So it would appear the UCase is the more
"efficient".

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

Sub test()
Const Str As String = "Rehau - w/Shield Cvr & Package "
Dim sTemp() As String
sTemp = Split(Str)
Dim IDa As String
Dim dTimer As Double
Dim i As Long
Dim j As Long
Const lReps As Long = 10000

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If sTemp(i) Like "[a-zA-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

End Sub
===========================
0.4375
1.125
=========================

--ron

Get First Letter of each word
 

On Wed, 20 Feb 2008 07:58:51 -0800, Les Stout wrote:

Hi Ron, I am batteling a bit with the way to call this function, could
you give me an example please...

Thanks

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

You mentioned using it in your "script" so I assume you have a macro, or other
function, running in which you want to call this.

So let's assume your string is in located in A1 of the active worksheet:

======================
Sub foo()
'Generate ID Code from string in A1
Dim IDCode As String

IDCode = ID(Range("A1"))
Debug.Print Range("a1"), IDCode

End Sub
=======================

And somewhere else in the module you have whichever of the two functions I
suggested.
--ron

Get First Letter of each word
 

Ron,
Try my modified version of your code and see what you get.
~ 1.19 vs. 0.73
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
'---

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub test()
Const Str As String = "Rehau - w/Shield Cvr & Package "
Dim sTemp() As String
sTemp = Split(Str)
Dim IDa As String
Dim dTimer As Long
Dim i As Long
Dim j As Long
Const lReps As Long = 100000 '<<<

dTimer = timeGetTime
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
IDa = vbNullString '<<<Note
Next j
MsgBox (timeGetTime - dTimer) / 1000

IDa = vbNullString
dTimer = timeGetTime
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If sTemp(i) Like "[a-zA-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
IDa = vbNullString '<<<Note
Next j
MsgBox (timeGetTime - dTimer) / 1000
End Sub
'---


"Ron Rosenfeld"
wrote in message
Well, I wondered about checking that. So I ran a routine identical except for
the comparison statement (see code below).

For 10,000 repetitions, the UCase variation took 0.4375 seconds, whereas the
[a-zA-Z] variant took 1.125 seconds. So it would appear the UCase is the more
"efficient".

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

Sub test()
Const Str As String = "Rehau - w/Shield Cvr & Package "
Dim sTemp() As String
sTemp = Split(Str)
Dim IDa As String
Dim dTimer As Double
Dim i As Long
Dim j As Long
Const lReps As Long = 10000

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If sTemp(i) Like "[a-zA-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

End Sub
===========================
0.4375
1.125
=========================

--ron

Get First Letter of each word
 

"Ron Rosenfeld" wrote in message
...
On Wed, 20 Feb 2008 12:04:03 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Function IDa(str As String) As String
Dim sTemp() As String
Dim i As Long
sTemp = Split(str)

For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i

End Function

Just a point of information... using the UCase function in the If-Then
statement above (to parse upper and lower case letters) will work as
shown,
but this should be more efficient:

If sTemp(i) Like "[a-zA-Z]*" Then

Rick

Well, I wondered about checking that. So I ran a routine identical except
for
the comparison statement (see code below).

For 10,000 repetitions, the UCase variation took 0.4375 seconds, whereas
the
[a-zA-Z] variant took 1.125 seconds. So it would appear the UCase is the
more
"efficient".

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

Sub test()
Const Str As String = "Rehau - w/Shield Cvr & Package "
Dim sTemp() As String
sTemp = Split(Str)
Dim IDa As String
Dim dTimer As Double
Dim i As Long
Dim j As Long
Const lReps As Long = 10000

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If sTemp(i) Like "[a-zA-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

End Sub
===========================
0.4375
1.125
=========================

--ron

Hi Ron,

I don't think those comparative tests are quite right.
Len(IDa) increases from 0-40000 in the first test, and continues up to 80000
in the second. Try reversing the order of the tests and the timings are
reversed (roughly)

If I reset IDa at the start of each loop -
For j = 1 To lReps
IDa = ""

I find both timings improve significantly with Like "[a-zA-Z]*" about twice
as quick as Ucase.

However if I comment this line
' IDa = IDa & UCase(Left(sTemp(i), 1))
the relative difference is much greater still in favour of Like "[a-zA-Z]*"
vs UCase

Regards,
Peter T

Get First Letter of each word
 

Function IDa(str As String) As String
Dim sTemp() As String
Dim i As Long
sTemp = Split(str)

For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i

End Function

Just a point of information... using the UCase function in the If-Then
statement above (to parse upper and lower case letters) will work as
shown,
but this should be more efficient:

If sTemp(i) Like "[a-zA-Z]*" Then

Rick

Well, I wondered about checking that. So I ran a routine identical except
for
the comparison statement (see code below).

For 10,000 repetitions, the UCase variation took 0.4375 seconds, whereas
the
[a-zA-Z] variant took 1.125 seconds. So it would appear the UCase is the
more
"efficient".

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

Sub test()
Const Str As String = "Rehau - w/Shield Cvr & Package "
Dim sTemp() As String
sTemp = Split(Str)
Dim IDa As String
Dim dTimer As Double
Dim i As Long
Dim j As Long
Const lReps As Long = 10000

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If UCase(sTemp(i)) Like "[A-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

dTimer = Timer
For j = 1 To lReps
For i = 0 To UBound(sTemp)
If sTemp(i) Like "[a-zA-Z]*" Then
IDa = IDa & UCase(Left(sTemp(i), 1))
End If
Next i
Next j

Debug.Print Timer - dTimer

End Sub
===========================
0.4375
1.125
=========================

As Peter pointed out, reverse the two tests and you get roughly the same
results, but now not using UCase comes out better. This may have something
to do with the increasing memory fragmentation taking place from all those
cumulative concatenation operations (the loops are so fast I am guessing
garbage collection is delayed until they have completed). In any event, just
looking at it logically, using the UCase function involves an extra function
call whereas once the Like operator's underlying code is kicked off, it
would seem that it would have very little extra to do to account for the
"a-z" check on top of the "A-Z" check... I can't see how executing the extra
function call would execute faster than running code without it, given that
both routines are making use of the Like operator.

Rick

Get First Letter of each word
 

Hi Jim,

For some reason your message wasn't visible to me when I posted mine some 20
min later. Had it been I wouldn't have duplicated with pretty much the same
observation (reset the variable IDa in each loop gives significantly
different results).

Regards,
Peter T

Get First Letter of each word
 

Hi Peter,
Guess I had better pay that bill my ISP sent me. <g
Regards,
Jim Cone


"Peter T"
<peter_t@discussions
wrote in message
Hi Jim,
For some reason your message wasn't visible to me when I posted mine some 20
min later. Had it been I wouldn't have duplicated with pretty much the same
observation (reset the variable IDa in each loop gives significantly
different results).
Regards,
Peter T

Get First Letter of each word
 

Perhaps you could pay mine too whilst you're at it <g

Regards,
Peter T

"Jim Cone" wrote in message
...

Hi Peter,
Guess I had better pay that bill my ISP sent me. <g
Regards,
Jim Cone


"Peter T"
<peter_t@discussions
wrote in message
Hi Jim,
For some reason your message wasn't visible to me when I posted mine some
20
min later. Had it been I wouldn't have duplicated with pretty much the
same
observation (reset the variable IDa in each loop gives significantly
different results).
Regards,
Peter T

Get First Letter of each word
 

On Wed, 20 Feb 2008 11:14:45 -0800, "Jim Cone" wrote:

Ron,
Try my modified version of your code and see what you get.
~ 1.19 vs. 0.73
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

Jim,

I see what you are doing and didn't realize that would happen.

~ 1.19 vs. 0.73

I get 0.739 vs 0.44

So the Ucase is definitely slower.

Thanks for that.
--ron

Get First Letter of each word
 

On Wed, 20 Feb 2008 15:05:16 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

As Peter pointed out, reverse the two tests and you get roughly the same
results, but now not using UCase comes out better. This may have something
to do with the increasing memory fragmentation taking place from all those
cumulative concatenation operations (the loops are so fast I am guessing
garbage collection is delayed until they have completed). In any event, just
looking at it logically, using the UCase function involves an extra function
call whereas once the Like operator's underlying code is kicked off, it
would seem that it would have very little extra to do to account for the
"a-z" check on top of the "A-Z" check... I can't see how executing the extra
function call would execute faster than running code without it, given that
both routines are making use of the Like operator.

Rick

See Jim Cone's post and my response to him. In brief, the Ucase *IS* slower.
--ron