Optimizing Batch / Bulk Tasks:
How to Trim or Remove Text in Excel (in Bulk)
- RIGHT(text, [num_chars]) – text = cell you want to modify, num_chars = numbers from the right you want to display
- LEFT(text, [num_chars]) – text = cell you want to modify, num_chars = numbers from the left you want to display
- LEN(text) – counts number of letters in cell
Combine these 3 functions to excel to bulk remove prefixes/suffixes, or any string of text, in excel
More Examples:
- =LEFT(S7,FIND(“El Cajon”,S7)-1) – To find “El Cajon” in cell S7, and give me only the text to the left of “El Cajon”. Note “-1” at the end to account for getting rid of the space)
More references:
Extract Text from cells in Excel – How to get any word from a cell in Excel
To pull out just the address out of “6618 Bridgegate Dr Spring, Tx.” for example:
=LEFT(A2,FIND(” “,A2,1+FIND(” “,A2,1+FIND(” “,A2)))-1)
Rename Multiple Files Using Excel or Google Docs
- Ctrl+A + F2 (Will rename all files to first file name + (number))
How to Copy Formula Without Changing Its Cell References In Excel
- Find and replace the “=” sign with “#”
- So “=A1*A2” becomes “#A1*A2”, for example
- Copy and paste your formula as necessary, and at end, copy and replace in reverse to restore “=” sign
Filter / Sort / Modify / Find Cells That Contain CAPS
- =EXACT(A1,UPPER(A1)) (Modify “A1” accordingly)
- Note: EXACT function compares two strings to determine whether they are equal. If both values are the same, it returns TRUE. Otherwise it returns FALSE.
- Note: UPPER function converts all letters in a cell to UPPERCASE.
- Note: LOWER function converts all letters in a cell to lowercase.
How To Merge Rows in a Column Into One Cell in Excel
Split Multiple Words in One Cell Into Separate Columns: Reference 1, Reference 2
- Solution: Use “Text to Columns” feature
FORMULAS
EXCEL FORMULA ERRORS EXPLAINED
#N/A Error for MATCH LOOKUP Explained
-
Lookup values are Text, and the table contains Numbers
If the lookup array contains numbers, and the value to look up is text, use a formula similar to the following:
=MATCH(—A5,ItemList,0)
The double unary (–) converts text to a number, and will work correctly even if the lookup values are numbers.
Turn a String Formula Into a “Real” Formula
CLEANING UP DATA
Deleting Empty Rows
- Select column in which blank values you’d like to delete the rows (If you do whole selection, you run the risk of deleting rows with pertinent data, just because one of the columns was blank
- Press F5 and select “Special”
- Select “Blanks” and click OK
- Go to Home tab and underneath “Cells” region, click “Delete” > “Delete Sheet Rows”
DEFINING VARIABLES
DUPLICATES
Excel – Finding Duplicates Across Sheets
Delete Duplicate Cells in Excel Across Tabs
How To Extract A Unique Distinct List From a Column in Excel
Categorize Each String under Group (1 Time), Based on String Content? – Thread 1, Thread 2, Thread 3
VBA