ysskrishna logoysskrishna

Chapter 1 of 7 · 3 min

Why sheet text gets messy, and how to fix it safely

The five kinds of messy text, which Text Power Tools tab fixes each, and the two habits that make bulk edits safe.

Text in a spreadsheet rarely starts clean. It arrives from CRM exports, form responses, copy-pasted web tables and colleagues who type differently. Before you can sort, filter, deduplicate or import it, you have to fix it. Formulas can do most of this, but each fix means a helper column, a formula, a copy and a paste-as-values. Text Power Tools does the fix in place, on the cells you select, from a sidebar.

Five kinds of messy text

SymptomExampleWhy it hurtsTab
Stray whitespace" Asha Rao ""Asha Rao" and " Asha Rao " don't match in lookups or duplicates checksClean
Inconsistent caseORDER id, order IDLooks sloppy; breaks case-sensitive matches and column namingCase
Missing fixed text1042 instead of SKU-1042Codes that another system expects with a prefixInsert
Unwanted characters$1,200.00, +91 98450-12345Numbers stored as text; phone formats that won't importClean
Data buried in free textCall asha@acme.io re invoiceYou need the email, not the noteExtract

Most clean-ups combine two or three of these. The recipe in chapter 7 runs them in order.

Opening the sidebar

  1. Install Text Power Tools from its Google Workspace Marketplace listing. It is free.
  2. In any spreadsheet, open Text Power Tools → Start from the menu. The add-on's own posts give this path; in most accounts add-ons appear under Extensions.
  3. The sidebar opens on the right with four tabs: Case, Insert, Clean and Extract. The footer reads "Built by ysskrishna".

The general flow is the same on every tab: select the range, choose the operation, set its options, click Apply.

How it changes your data

Text Power Tools rewrites the selected cells in place. Extract, for example, replaces each cell's content with the matches it found. That is what makes it fast, and also why the next two habits matter.

Two habits for safe bulk edits

  1. Work on a copy first. Right-click the tab → Duplicate, or practise in the Text Power Tools - Examples Workbook. The add-on's own guidance says the same: if a clean-up feels even slightly destructive, test it first.
  2. Select only what you mean to change. Select the data column, not the whole sheet. Leave header rows out of the selection unless you want them changed too.

Undo is your third line of defence: Sheets' Edit → Undo (Ctrl+Z / ⌘Z) normally reverses the last change.

Formulas or the add-on?

Use a formula when…Use Text Power Tools when…
The source keeps updating and the result must update tooYou are fixing data once before using it
You need the original next to the resultYou want the original replaced
The rule is simple, like =TRIM(A2)You need several steps, naming styles or pattern extraction

Formulas such as TRIM, PROPER, UPPER and REGEXEXTRACT are great for live data. For a one-off clean-up of 5,000 rows, a sidebar that rewrites the values is faster and leaves no helper columns behind.

Step by step: your first clean-up

  1. Make a new sheet and paste these into A1:A4 (sample data):
  Asha Rao
RAJ  KUMAR
li wei
  maria GARCIA
  1. Select A1:A4.
  2. Open Clean, choose Trim both, click Apply. Leading and trailing spaces are gone.
  3. Open Case, choose Capitalized Case, click Apply. You should see Asha Rao, Raj Kumar, Li Wei, Maria Garcia.
  4. Notice the double space inside Raj Kumar: trimming only touches the ends. Chapter 2 covers inner spaces.

Quick check

  1. A lookup fails although the names look identical. Which tab do you try first?Clean: trailing or leading spaces are the usual cause.

  2. Does Text Power Tools write results to a new column?No. It rewrites the selected cells in place, which is why you work on a copy first.

  3. Your data refreshes daily from a connector. Formula or add-on?A formula, so the clean result updates with the source.

Continue exploring

ProductGoogle WorkspaceGoogle Sheets Text Power Tools

Google Sheets Text Power Tools

Fix messy sheet text in place. Case, trim, strip, and extract without leaving the grid.
Google Sheets Text Power Tools
Text & Data Conversion
Google Workspace Add-ons
Google Sheets
ProductGoogle WorkspaceGoogle Sheets Tab Manager

Google Sheets Tab Manager

Your workbook outgrew the tab bar. Clean it up in batches.
Google Sheets Tab Manager
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Import Markdown, JSON, HTML and CSV tables into Google Sheets without broken columns, and export any range as clean Markdown, JSON, HTML or CSV.

Google Sheets Table Converter
Google Sheets
+1

6 chapters · 16 min

BlogCover image for How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

Google Sheets Tab Manager
Google Workspace Add-ons
+1
2 min read
ProductGoogle WorkspaceGoogle Sheets Merge Tools

Google Sheets Merge Tools

Repeatable merges in Google Sheets, without the misclick marathon.
Google Sheets Merge Tools
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Delete multiple tabs in Google Sheets at once, hide and unhide in bulk, rename with find and replace, colour and sort tabs A–Z with the free Sheets Tab Manager.

Google Sheets Tab Manager
Google Sheets
+1

6 chapters · 24 min