Information on using Google Sheets

Search and Replace Functions

‘=Substitute‘ is a simple function for find and replace

The following hints work in both the regular search and replace module and in ‘=Regexreplace‘

• Put the search sting in ” “
• ‘.’ is wild card for one character
• ‘.+’ is wild card for multiple characters
• ‘\s’ is wild card for a space
• ‘[A-Z]’ will find and character from A-Z, use ‘[a-z]’ for a-z
• Putting ( ) around a section means that you want it to show up in the replace result
  • Use the ‘$x’ where x is the number of the ( ) to place the section in the replace result
  • Example is $1 for first block and $2 for second block
  • Example 916-456-7894, search “(.+)-(.+)-(.+)” , replace “$1 $2 $3” get you 916 456 7894
  • Example Specialty-Other, find (.+)-.+, replace $1, gets you Specialty

openpyxl (for working with Excel files and LibreCalc files with xlsx extensions)

• Getting a worksheet by name: wb.sheet_by_name(“_____”)
• Getting a worksheet by name: wb.sheet_by_id(0)
• getting the active worksheet{ wb.active

Getting and Setting a Cell’s Value

• Two options:
  • sheet.getRange(row@,col#).getValue()
  • sheet.getRange(“cell location, ie B12”).getValue()
• sheet.getRange(either option above).setValue(“value here”)

Find Similar Text – How to VLOOKUP, MATCH Inconsistent Text

• Video instructions for installing and using
• Install findsimilartext to google sheets to find approximate matches
• Use xMatch for confirming lists for example in RPA retired lists
• Use ISNUMBER with XMATCH to get True or False answers

Regular Expressions

Site to test a regular expressions and a cheat sheet <pythex.org>

Query Statements

• =QUERY(MainList!A1:C,”SELECT * WHERE LOWER (A) LIKE LOWER (‘%”&B1&”%’)”, -1)
• https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/

Pull Year from Date

• use “=YEAR(Column Letter)” without the quotes to create a new column with just the year

=YEAR(Column Letter)