[Solved] Extract uppercase words to a different cell

Discuss the spreadsheet application

[Solved] Extract uppercase words to a different cell

Postby fiona87 » Wed Nov 25, 2020 6:27 pm

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!
Last edited by MrProgrammer on Wed Dec 02, 2020 5:50 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.7 on Windows 10
fiona87
 
Posts: 8
Joined: Mon Oct 19, 2020 1:32 pm

Re: Extract uppercase words to a different cell

Postby lader » Wed Nov 25, 2020 8:09 pm

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
LibreOffice 6.4.7.2 on Ubuntu 20.04.4 LTS
lader
 
Posts: 31
Joined: Mon Jul 02, 2018 6:10 pm

Re: Extract uppercase words to a different cell

Postby fiona87 » Wed Nov 25, 2020 10:16 pm

Thanks, I am afraid however that I have no idea how to use that (as in how to add o use a macro) :(
OpenOffice 4.1.7 on Windows 10
fiona87
 
Posts: 8
Joined: Mon Oct 19, 2020 1:32 pm

Re: Extract uppercase words to a different cell

Postby MrProgrammer » Thu Nov 26, 2020 6:48 pm

fiona87 wrote: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.
Screen Shot 2020-11-26 at 10.42.46 copy.jpg
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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3864
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Extract uppercase words to a different cell

Postby lader » Fri Nov 27, 2020 6:07 pm

One more try
Regex - capitalize word.ods
(12.62 KiB) Downloaded 12 times
LibreOffice 6.4.7.2 on Ubuntu 20.04.4 LTS
lader
 
Posts: 31
Joined: Mon Jul 02, 2018 6:10 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests