ysskrishna logoysskrishna

Chapter 2 of 7 · 3 min

Trim and clean: spaces, digits and unwanted characters

Use the Clean tab's three groups (Whitespace, Keep / Remove, Strip specific characters) to get text into a shape other tools accept.

The Clean tab handles the clean-ups people most often do by hand. It has three groups, shown in the sidebar exactly like this:

  • WHITESPACE: Trim start, Trim end, Trim both.
  • KEEP / REMOVE: Keep alphanumeric only, Keep letters only, Keep digits only, Remove all digits.
  • STRIP SPECIFIC CHARACTERS: a field labelled "Characters to remove (each character)" with an Apply button.

The listing notes that these rules are Unicode-aware where applicable, so letters such as é or ß count as letters.

Whitespace

OptionRemovesUse for
Trim startSpaces before the textIndented imports
Trim endSpaces after the textValues copied from web tables, which often end in a space
Trim bothBothThe default choice for names, emails and IDs

Trimming does not touch spaces inside the text. "Raj Kumar" keeps its double space. To collapse inner runs of spaces, use Sheets itself: Edit → Find and replace, tick Search using regular expressions, find {2,} and replace with a single space. Or, for live data, =TRIM(A2), since Google Sheets' TRIM also collapses repeated inner spaces.

Keep or remove

These options filter each cell down to a character class:

Option"Order #A-1042 (paid)" becomes
Keep alphanumeric onlyOrderA1042paid
Keep letters onlyOrderApaid
Keep digits only1042
Remove all digitsOrder #A- (paid)

Expected results based on the option names; check against your own data.

Keep digits only is the workhorse. It turns phone numbers, IDs and amounts stored as text into plain digits:

Before (sample)After Keep digits only
+91 98450-12345919845012345
(555) 010-20305550102030
INV-000731000731

Watch for two traps. Decimal points are not digits, so $1,200.50 becomes 120050, a hundred times too big. And leading zeros may disappear if Sheets then treats the result as a number. Format the column as Plain text first (Format → Number → Plain text) when zeros matter.

Strip specific characters

When you only want to remove a few characters, type them into "Characters to remove (each character)". Every character you type is removed wherever it appears. The sidebar's placeholder shows the idea: $,%&.

GoalType"$1,200.50" becomes
Amount as a number$,1200.50
Remove quotes"'text without quotes
Remove brackets()[]text without brackets

This is safer than Keep digits only for money, because the decimal point survives.

Step by step: clean an amount column

  1. Paste sample amounts into a column: $1,200.50, $85.00, $12,000.
  2. Select the cells. Open Clean.
  3. Click Trim both, then Apply.
  4. In "Characters to remove", type $, and click Apply next to the field.
  5. Select the column and use Format → Number → Number. The values now sum correctly.

Which option, when

  • Names, emails, IDs: Trim both.
  • Phone numbers for an import: Trim both, then Keep digits only (format the column as plain text first).
  • Money: Strip specific characters with your currency symbol and thousands separator.
  • Codes with letters and digits: Keep alphanumeric only.

Quick check

  1. Why not use Keep digits only on "$1,200.50"?It removes the decimal point too, giving 120050. Strip $, instead.

  2. Trim both left a double space in the middle. How do you fix it?Sheets Find and replace with a regex such as {2,} → one space, or =TRIM() for live data.

  3. Phone numbers lost their leading zero. What should you have done?Format the column as Plain text before cleaning.

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