[Solved] Extract uppercase words to a different cell

[MrProgrammer]

Hi there, I have a spreadsheet where I need to extract capitalized words from one column to another column. For example, column A might say "Chocolate cookies OREO", and I would like to have "Chocolate cookies OREO" on that column and "OREO" on the other column. Any idea of how this could be done?

Thanks in advance!

[lader]

This function (macro) finds the first word written in capital letters

Code: Select all   Expand viewCollapse view

Function BigWord(W$) as String
      Dim V, L%, Q, i%
      BigWord = ""
      V = Split(W," ")
      L = UBound(V)
      For i = 0 To L
         Q = V(i)
         If Q=UCase(Q) Then
            BigWord = Q
            Exit Function
         EndIf
      Next      
End Function

[fiona87]

Thanks, I am afraid however that I have no idea how to use that (as in how to add o use a macro)

[MrProgrammer]

I have a spreadsheet where I need to extract capitalized words from one column to another column. For example, column A might say "Chocolate cookies OREO", and I would like to have "Chocolate cookies OREO" on that column and "OREO" on the other column.

Select "that column" by clicking the column letter at its head. Edit → Copy. Select "the other column". Edit → Paste. Edit → Find & Replace → More options, select Match case and Current selection only and Regular Expressions, Search for \b[^ ]*[a-z][^ ]*\b, Replace with «nothing», Replace All. I recommend unchecking Match case and Current selection only and Regular expressions, before clicking Close.

The regular expression backslash b open-bracket caret space close-bracket asterisk open-bracket a hyphen z close-bracket open-bracket caret space close-bracket asterisk backslash b means "word with a lower case letter". Those are replaced with nothing (deleted) in the new column, leaving only upper case words. If a cell has five words and two of them are upper case, Find&Replace keeps them both. You can use Find&Replace again to remove excess spaces, if you like.

Find&Replace settings

For any additional assistance, attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). If this is an action which you'd like to repeat, record a macro to copy, paste, and replace.
.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[lader]

One more try

Regex - capitalize word.ods

(12.62 KiB) Downloaded 12 times