Get First Letter of each word

L

Les Stout

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
 
R

Ron Rosenfeld

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
Click to expand...


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
 
P

Peter T

Just a heads up -
The Split function appeared, I believe, in XL2002.
Click to expand...

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

Regards,
Peter T
 
L

Les Stout

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
 
R

Rick Rothstein \(MVP - VB\)

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

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
 
R

Rick Rothstein \(MVP - VB\)

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
J

Jim Cone

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
 
P

Peter T

Ron Rosenfeld said:
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
Click to expand...

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
Click to expand...

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
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

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
Click to expand...

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
Click to expand...

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
=========================
Click to expand...

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
 
P

Peter T

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
 
J

Jim Cone

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
 
R

Ron Rosenfeld

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
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract First Letter from each word in a Text String 12
Capitalize first letter of each word 3
error in code: capitalize first letter of a word 6
Capitalize first letter of each word 2
Why won't first letter of first word of each bulleted list capital 1
First letter of each word 3
First Letter of Each Word 3
Create Acronym (Extract first letter of each word) 16

Top