Google Sheets Examples: Working with Numbers and Text

🧠 Quick Study Guide: Google Sheets - Numbers & Text

• 🔢 Working with Numbers:
  • ✨ Basic Arithmetic: Use operators like `+`, `-`, `*`, `/` directly in cells (e.g., `=A1+B1`).
  • 📈 Common Functions: Utilize functions like `=SUM(range)`, `=AVERAGE(range)`, `=COUNT(range)`, `=MAX(range)`, `=MIN(range)`.
  • 🧮 Rounding & Integers: Functions include `=ROUND(number, [places])`, `=ROUNDUP()`, `=ROUNDDOWN()`, `=INT(number)` (rounds down to nearest integer), `=TRUNC(number, [places])` (truncates to integer or specified decimal places).
  • 📊 Formatting: Apply currency, percentage, date, or number formats via the 'Format' menu.
• 📝 Working with Text:
  • 🔗 Concatenation: Combine text strings using the `&` operator (e.g., `=A2&" "&B2`) or the `=CONCATENATE(text1, text2, ...)` function.
  • ✂️ Extracting Text:
    • `=LEFT(string, [num_chars])`: Extracts characters from the beginning.
    • `=RIGHT(string, [num_chars])`: Extracts characters from the end.
    • `=MID(string, start_index, num_chars)`: Extracts characters from the middle.
  • 🔍 Finding & Replacing: `=FIND(search_for, text_to_search, [starting_at])` returns the starting position. `=REPLACE(text, position, length, new_text)` replaces a portion of text.
  • 🔡 Case Conversion: `=LOWER(text)`, `=UPPER(text)`, `=PROPER(text)` (capitalizes first letter of each word).
  • 🧹 Cleaning Text: `=TRIM(text)` removes leading, trailing, and excessive spaces between words.
  • 📏 Length: `=LEN(text)` returns the number of characters in a string.
• 🤝 Combining Numbers and Text:
  • 💡 When combining, numbers might lose their specific formatting. Use the `=TEXT(value, format)` function to preserve numerical formatting (e.g., `=A1&" - "&TEXT(B1, "$0.00")`).
  • ✨ Conditional Formatting: Use rules to apply formatting based on cell values (numbers) or text content.
• 📍 Cell References:
  • ➡️ Relative (A1): Changes when copied.
  • 🔒 Absolute ($A$1): Stays fixed when copied.
  • ↔️ Mixed ($A1 or A$1): Fixes either row or column.

🎯 Practice Quiz

1. Which formula would correctly combine the text in cell A1 ("First") with the text in cell B1 ("Name") to display "First Name"?
   A. `=CONCATENATE(A1, B1)`
   B. `=A1+B1`
   C. `=A1&" "&B1`
   D. `=JOIN(A1, B1)`
   
   
2. You have "Product-001" in cell C1. Which formula would extract "001" from this string?
   A. `=LEFT(C1, 3)`
   B. `=RIGHT(C1, 3)`
   C. `=MID(C1, 8, 3)`
   D. `=EXTRACT(C1, 8, 3)`
   
   
3. Cell D1 contains the number 1234.5678. You want to display it as "Total: $1,234.57" within a text string. Which formula achieves this?
   A. `="Total: "&D1`
   B. `="Total: "&TEXT(D1, "$#,##0.00")`
   C. `="Total: "&DOLLAR(D1, 2)`
   D. `="Total: "&FIXED(D1, 2)`
   
   
4. Which function is used to remove extra spaces from a text string, including leading, trailing, and multiple spaces between words?
   A. `=CLEAN()`
   B. `=TRIM()`
   C. `=REMOVE.SPACES()`
   D. `=COMPACT()`
   
   
5. If cell E1 contains "GOOGLE SHEETS" and you want to convert it to "Google Sheets", which function would you use?
   A. `=LOWER(E1)`
   B. `=UPPER(E1)`
   C. `=PROPER(E1)`
   D. `=CAPITALIZE(E1)`
   
   
6. To sum the values in cells F1 to F10, which is the most efficient formula?
   A. `=F1+F2+...+F10`
   B. `=ADD(F1:F10)`
   C. `=TOTAL(F1:F10)`
   D. `=SUM(F1:F10)`
   
   
7. Cell G1 contains the text "Hello World". What will `=LEN(G1)` return?
   A. 10
   B. 11
   C. 12
   D. 13