Re: Finding words with capital letters and numbers in them

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

As for the missing first letter... that's what happens when you rush. My brother is due any minute for dinner, so I had to get ready... I made a last minute change from my original delimiter of a comma-space to just the space that you used and I forgot to adjust the code for the length difference. I change my code to include a Delimiter constant (set to equal a space; but changeable to any combination of characters) and put code in to account for it. As for the multi-line problem... I never even gave it a thought originally. Below is a revised function that handles both problems (thanks for catching them)...

Function GetCapWords(R As Range) As String
Dim X As Long
Dim Words() As String
Const Delimiter = " "
Words = Split(Replace(Replace(R.Value, vbLf, " "), vbCr, " "))
For X = 0 To UBound(Words)
If Words(X) = UCase(Words(X)) Then
GetCapWords = GetCapWords & Delimiter & Words(X)
End If
Next
If Len(GetCapWords) > 0 Then
GetCapWords = Mid(GetCapWords, 1 + Len(Delimiter))
End If
End Function

Rick


"Ron Rosenfeld" <ronrosenfeld@xxxxxxxxxx> wrote in message news:n5buk3hmqjv7qele8iu976rhebaubp7kqf@xxxxxxxxxx
On Thu, 29 Nov 2007 15:39:15 -0500, "Rick Rothstein \(MVP - VB\)"
<rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote:

Function GetCapWords(R As Range) As String
Dim X As Long
Dim Words() As String
Words = Split(R, " ")
For X = 0 To UBound(Words)
If Words(X) = UCase(Words(X)) Then
GetCapWords = GetCapWords & " " & Words(X)
End If
Next
If Len(GetCapWords) > 0 Then
GetCapWords = Mid(GetCapWords, 3)
End If
End Function

What surprises me a little, Ron, is that it looks shorter than the RegEx
solution... not what I would have expected.

Rick,

Given this string:

The TKRTC value in the 765TW field is not the default

Your routine, on my machine, returns:
KRTC 765TW
(The initial T is missing)

Also, given a multiline string:

The TKRTC
value in the 765TW field is not the default

(with no space after TKRTC), your routine returns:
65TW

missing TKRTC and also again missing the first character.
--ron

.

Relevant Pages

  • Problems Managing Required Field and Form Events
    ... Dim CheckVal As String ... If CheckVal = "Missing" Then ... Private Sub Form_BeforeUpdate ... Do you want to close the database object anyway? ...
    (microsoft.public.access.formscoding)
  • Problems Managing Required Field and Form Events
    ... Dim CheckVal As String ... If CheckVal = "Missing" Then ... Private Sub Form_BeforeUpdate ... Do you want to close the database object anyway? ...
    (microsoft.public.access.formscoding)
  • A Problem Using Hashes
    ... I am inching my way through INTERMEDIATE PERL and tried using hashes to ... Use of uninitialized value in string eq at chap4-take6.pl line 49. ... gilligan is missing lotion ... professor is missing coat ...
    (perl.beginners)
  • Re: Text to columns and comma delimited text
    ... missing fields. ... a pattern that matches the cells that you know about (eg can you find ... individual pieces of data within that string into individual columns. ... Work Type: ...
    (microsoft.public.excel.programming)
  • Re: Unicode question
    ... You're missing the existence and continued use of UTF-8 and UTF-16. ... I want to use an extended string that's good enough to cover UCS-4 ... sometimes multiple characters must be treated as one unit. ...
    (borland.public.delphi.non-technical)