Pick the UCase letters only

[Liv Manto]

I have a million record of a single field that has combination of funny characters and letters in it.

I only want to pick out the Uppercase data.

I dont want to turn them into UCASE.

For example:

{sectl}SECTION I<qa>\nPREFLIGHT (05-20-10)\

I only want 'SECTION I PREFLIGHT"

Any ideas, please?

 

[DALeffler]

A million records? Ooof!!

This may not be fast...

Function fncFindUC()
Dim str as String, Result as String
Dim I as Integer, J as Integer, CharCode as Integer

str = "{sectl}SECTION I<qa>\nPREFLIGHT (05-20-10)\"

For I = 1 to Len(str) 'replace all LC characters in str with sp " "
     CharCode = Asc(Mid(str, I, 1)
     If CharCode > 64 And CharCode < 91 Then
          Result = Result & Mid(str, I, 1)
     Else
          Result = Result & " "
     End If
Next I

Result = Trim(Result)

While InStr(1, Result, "  ")      'look for double space...
     J = InStr(1, Result, "  ")
     Result = Mid(Result, 1, J) & Mid(Result, J + 2)    'eliminate double space
Wend

Debug.Print Result

End Function

SECTION I PREFLIGHT

You'll have to modify the above to make a function a query can call - and it will be slow - but that ought to give you some ideas.

PS: Watch that While/Wend loop: it's very easy to cause an infinite loop!

(Edit) If you do try this, how long did it take for a million records?

 

[Liv Manto]

Thanks Doug. A lot.

, Will report you time later, still working on something else.