Extract Proper Case words

W

webbug08

New Member
Joined
Aug 17, 2014
Messages
3
Hello All,

I have been using following code to extract all upper case words in a string but the problem is I can not extract words which are proper. For example

This is GOOD

Present output: GOOD
Desired Output: This GOOD

Code: 
Function UpperWords(str As Variant) As String
    Dim i As Integer, sTemp As String, StrTmp As String
    For i = 0 To UBound(Split(str, " "))
        StrTmp = Split(str, " ")(i)
        If UCase(StrTmp) = StrTmp Then sTemp = sTemp & " " & StrTmp
    Next i
    UpperWords = Trim(sTemp)
End Function


Can any one please suggest what can be suitable modification in this case?

Thanks,
Webbug
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Try
Code: 
Function ProperUPPERWords(str As Variant) As String
    Dim i As Integer, Words As Variant, StrTmp As String
    
    Words = Split(str, " ")
    
    For i = 0 To UBound(Words)
        StrTmp = Words(i)
        If LCase(StrTmp) = StrTmp Then
            Words(i) = vbNullString
        End If
    Next i
    ProperUPPERWords = WorksheetFunction.Trim(Join(Words, " "))
End Function
 
Upvote 0
This works very well but the only problem is that, the earlier function also reported the numbers in the string but this is not. Is there any work around it?

Thanks,
Webbug
mikerickson said:
Try
Code: 
Function ProperUPPERWords(str As Variant) As String
    Dim i As Integer, Words As Variant, StrTmp As String
    
    Words = Split(str, " ")
    
    For i = 0 To UBound(Words)
        StrTmp = Words(i)
        If LCase(StrTmp) = StrTmp Then
            Words(i) = vbNullString
        End If
    Next i
    ProperUPPERWords = WorksheetFunction.Trim(Join(Words, " "))
End Function
Click to expand...
 
Upvote 0
mikerickson said:
Try
Code: 
Function ProperUPPERWords(str As Variant) As String
    Dim i As Integer, Words As Variant, StrTmp As String
    
    Words = Split(str, " ")
    
    For i = 0 To UBound(Words)
        StrTmp = Words(i)
        [COLOR=#ff0000][B]If LCase(StrTmp) = StrTmp Then[/B][/COLOR]
            Words(i) = vbNullString
        End If
    Next i
    ProperUPPERWords = WorksheetFunction.Trim(Join(Words, " "))
End Function
Click to expand...
That test assumes perfect typing and won't catch words like ONe, TwO or thrEE. If such words could exist and if the OP would want them removed (as opposed to being left in so they could be seen and corrected), then this replacement code line could be used...
Code: 
If StrTmp Like "[!A-Z]*" Or Mid(StrTmp, 2) Like "*[!a-z]*" Then

EDIT NOTE: Just saw the OP's latest posting (Message #3)... the above suggested replacement will handle numbers in the text.
 
Last edited:
Upvote 0
Peter_SSs said:
Rick, perhaps I've mis-understood but that doesn't produce the stated desired output.
Click to expand...

I forgot about testing for all upper case letters.:oops: I think this replacement line will work...
Code: 
If StrTmp Like "[!A-Z]*" Or (Mid(StrTmp, 2) Like "*[!a-z]*" And UCase(StrTmp) <> StrTmp) Then
 
Upvote 0
The test in my UDF could be changed
Code: 
If Not ((UCase(StrTmp) = StrTmp Or StrConv(StrTmp, vbProperCase) = StrTmp) And Not (StrTmp Like "*[!A-za-z]*")) Then
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Finding one word in a string and outputting the word in the same row
Replies
3
Views
260
Peter_SSs
Peter_SSs
F
  • Question
vba to extract address
Replies
7
Views
842
Peter_SSs
Peter_SSs
MyHanhCB
  • Solved
Function that returns an array?
Replies
9
Views
484
kevin9999
K
A
  • Solved
Date written need to extract into date format
2
Replies
15
Views
378
Joe4
Joe4
netspeedz
  • Solved
Extracting Data From Kitco.com [Using VBA]
Replies
6
Views
430
netspeedz
netspeedz

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top