CODEWITHSUNDEEP
excelformulauppercase
Caracks
Caracks

Reputation: 49

Extract Uppercase Words on Excel

I have 167 cells, and each cell a string of text, where each cell has a word that's all uppercase and I need to copy only that word to a new cell. I've tried the EXACT formula, but it only identifies if the text has an uppercase word and returns with a "true" or "false".

The result should be:

A1 B1
The quick brown fox JUMPS over the lazy dog JUMPS

Upvotes: 1

Views: 10285

Answers (2)

QHarr
QHarr

Reputation: 84465

You can use a regex to extract the uppercase words. This can be deployed as an UDF in the sheet

Option Explicit
Public Sub TEST()
    Dim tests(), i As Long
    tests = Array("The lazy LAD was sorry for the debacle", "She wept as her FLAXEN hair tumbled down the parapet")

    For i = LBound(tests) To UBound(tests)
        Debug.Print GetString(tests(i))
    Next
End Sub

Public Function GetString(ByVal inputString As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .Pattern = "\b[A-Z]+\b"
        If .TEST(inputString) Then
            If len(.Execute(inputString)(0)) > 1 Then
                GetString = .Execute(inputString)(0)
                Exit Function
            End If     
        End If
        GetString = vbNullString
    End With
End Function

Regex:

Try it here.

\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)

Match a single character present in the list below [A-Z]+

+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

A-Z a single character in the range between A (index 65) and Z (index 90) (case sensitive)

\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)

In sheet:

enter image description here

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Try the following User Defined Function:

Public Function grabber(s As String) As String
    grabber = ""
    arry = Split(s, " ")
    For Each a In arry
        If a = UCase(a) Then
            grabber = a
            Exit Function
        End If
    Next a
End Function

It will extract the first uppercase word in the cell.

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

(this code can easily be modified to extract all the uppercase words from a sentence)

Upvotes: 4

Related Questions