How to get the Right function to return a string that has UPPERCASE letters only

adj · #1 08-28-2014, 11:24 AM

How do I format the return string for the Right function to find UPPERCASE letters? For example, I tried the following, [A-Z], which didn't work:

SelectedText = Selection.Text
Right(SelectedText, 2) = "[A-Z][A-Z]"

Thanks

macropod · #2 08-28-2014, 09:01 PM

You could use code like:

Code:

Sub Demo()
Application.ScreenUpdating = False
With Selection.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[A-Z]{2,}"
    .Replacement.Text = ""
    .Forward = False
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  If .Find.Found Then
    MsgBox Right(.Text, 2)
  End If
End With
Application.ScreenUpdating = True
End Sub

adj · #3 08-29-2014, 04:19 AM

Thanks a lot, Paul/macropod. I'll see if I can make that work. I need to find all instances of [A-Z][A-Z]-[0-9] in a doc. (and get surrounding characters, if present, that are similar*) and somehow list them (either by deleting everything else in the doc or copying them into a blank file). I'd tried the Find functionality, but I couldn't get it to do what I needed. I'll keep experimenting.
*e.g., BC-3, ABC-5, BC-003, DA-378-A

gmayor · #4 08-29-2014, 07:15 AM

[A-Z0-9-]{4,7} will find all the quoted examples, but it is a rather blunt instrument that will find upper case words and numbers of 4 to 7 characters also.

You are probably going to have to use two searches, because of the last item in your examples. For the others

[A-Z]{2,3}-[0-9]{1,3}

and for the odd one out

[A-Z]{2,3}-[0-9]{1,3}-[A-Z]{1,}

See http://www.gmayor.com/replace_using_wildcards.htm

adj · #5 08-29-2014, 07:35 AM

Thanks a lot, Graham. I've tried a couple of different major approaches (one approach is Find; 2nd approach is to have the macro look at each word). I'm going to try to figure out which approach is best (or whether a combo is necessary). I think you're right that it'll probably take a couple of searches with the Find. No matter which approach I find works best, I still am very curious as to how to know when the string found by the Right function is any UPPERCASE letter--i.e., what to use between the quotes (after the '=' sign) in the Right function. Thanks again.

adj · #6 08-29-2014, 08:12 AM

Below is the current code (which I adapted from code I found online):

Code:

 
Selection.HomeKey Unit:=wdStory ' line 1
For Each rngRange In ActiveDocument.StoryRanges ' line 2For Each rngSentence In rngRange.Sentences ' line 3For Each rngWord In rngSentence.Words ' line 4rngWord.Select ' line 5
SelectedText = Selection.Text ' line 6
 
If Right(SelectedText, 2) = "[A-Z][A-Z]" Then ' line 7
Else ' line 8
End If ' line 9
Next rngWord ' line 10
Next rngSentence ' line 11
Next rngRange ' line 12

In line 7 above, I'm trying to get the macro to identify the types of text strings I'm looking for (e.g., BC-3, ABC-5, BC-003, DA-378-A). I realize that maybe the For Each...Next (word) is not the best way to go about this, but it seems to work pretty well (as the main approach vs. using the Find approach).

Should I forget about the For Each...Next approach and stick with the Find approach?

Thanks

macropod · #7 08-29-2014, 03:47 PM

For the Find, I'd suggest using:
.Text = "[A-Z]{2,}-[0-9A-Z\-]{1,}"
That should be sufficient to return all the strings you're concerned with. However, it's far from clear now what you want to do with them. You originally said:

Quote:

How do I format the return string for the Right function to find UPPERCASE letters

and provided pseudo-code for getting the two right-most characters. The code I posted does that, but I really have no idea what you want to do with what the Find returns.

gmayor · #8 08-29-2014, 09:32 PM

Quote:

Originally Posted by adj

I've tried a couple of different major approaches (one approach is Find; 2nd approach is to have the macro look at each word). I'm going to try to figure out which approach is best (or whether a combo is necessary) ........ the Right function is any UPPERCASE letter--i.e., what to use between the quotes (after the '=' sign) in the Right function.

I don't see how processing by word is going to help. It still has the same basic issue that there are two disparate data sequence type BC-3, ABC-5, BC-003 and DA-378-A, which will require separate processes.

Also processing sequentially in this manner is very slow compared with FIND.

One way to check whether the Right string value is upper case is to compare the upper case version with the actual value e.g.

If Right(strText, 1) = Right(UCase(strText), 1) Then
MsgBox "Upper Case"
Else
MsgBox "Lower case"
End If

As Paul also has asked, what are you going to do with the strings found?

adj · #9 08-30-2014, 10:09 AM

Sorry I wasn't clear in post #3, where I wrote: I need to find all instances of [A-Z][A-Z]-[0-9] in a doc. (and get surrounding characters, if present, that are similar*) and somehow list them (either by deleting everything else in the doc or copying them into a blank file).
*e.g., BC-3, ABC-5, BC-003, DA-378-A

The examples I gave are contained within regular text paragraphs, and I just need to create a list of all the items (e.g., AB-1, DEM-0003, etc.).

Thanks very much

macropod · #10 08-30-2014, 09:21 PM

Try:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim StrOut As String
StrOut = " "
With Selection.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[A-Z]{2,}-[0-9A-Z\-]{1,}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    If InStr(StrOut, " " & .Text & " ") = 0 Then StrOut = StrOut & .Text & " "
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
  .InsertAfter vbCr & Replace(Trim(StrOut), " ", ", ") & vbCr
End With
Application.ScreenUpdating = True
End Sub

With this code, each item will be listed once in the output, regardless of how often it appears in the input.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Uppercase formula?	clements55	Excel	1	08-20-2014 11:34 PM
Way to search for a string in text file, pull out everything until another string?	omahadivision	Excel Programming	12	11-23-2013 12:10 PM
Can I use the filter function to separate letters into separate files?	drhauser	Mail Merge	2	12-14-2011 02:18 PM
Day and Month to Uppercase	Andy2011	Word VBA	1	07-22-2011 04:34 PM
Macro or Function to know wether a string is included in a text	Eduardo	Word VBA	5	06-15-2009 01:55 AM