DataScrub Complete User Guide

103 ribbon commands in this guide. Search filters the feature tables.

Quick start

DataScrub is portable: one launcher contains the 32-bit and 64-bit add-ins plus this guide. It does not require administrator access or permanent installation.

Double-click DataScrub-Portable-v1.0.0.exe or the unversioned launcher inside the ZIP.
Select Open DataScrub in Excel. The launcher detects Excel’s architecture, verifies the embedded XLL, and loads it for this Excel session.
Open or create a workbook. Select only the cells, rows, or columns you intend to inspect or change.
Open the DataScrub ribbon tab. Begin with Health Scan or Quick Clean, or choose a focused command from a task menu.
Review the dialog preview and output location. Apply the change, check representative rows and totals, then save the workbook.

Three-day trial: merely opening the launcher or Excel does not start it. The 72-hour period begins when you invoke your first DataScrub feature and the licensing server confirms the device. Internet is required to start; after verification, the signed local cache supports up to 24 hours offline. Reinstalling, deleting local trial files, or moving the clock backward does not create a new trial and may require another online check.

DataScrub Portable launcher with Open in Excel, User Guide, license, support, and cleanup controls — The portable launcher is the customer entry point. “Open DataScrub in Excel” loads the matching XLL only for the current Excel session.

Ribbon map

The final ribbon uses eight workflow groups. Menus keep the full feature set available without crowding Excel’s ribbon.

StartScan, guided clean, pipelines, Undo TextWhitespace, case, split, extract, create NumbersGenerate, calculate, convert, repair DatesConvert, extract, shift, compare, age CleanupDuplicates, blanks, formulas, standardize ContactsPhones, emails, countries, names, URLs ImportCSV, text, PDF, Word, merge, clipboard SupportLicense, updates, help

After an update: close every Excel window and reopen DataScrub from the portable launcher. Excel caches RibbonX, so an already-open Excel process can continue showing the previous ribbon layout.

Safety, previews, outputs, and Undo

Indicator	Meaning	What to check
In place	Changes eligible selected cells.	Formulas are preserved or skipped unless the command explicitly converts/replaces them. Confirm the selection.
Output	Writes to a new or chosen destination.	Existing data triggers a warning and the actual destination is snapshotted for Undo.
Structural	Inserts or deletes whole rows/columns.	One-level structural Undo; unsafe protected, table, merged, last-column, or oversized cases are declined.
Read only	Scans, previews, reports, or opens help.	No workbook data changes until a separate Apply/Fix action.

DataScrub Undo is separate from Excel Undo. It reverses the most recent DataScrub operation and is tied to the originating workbook and worksheet.
Save a copy of important workbooks and test a representative range first.
“No cells selected” means cancel, select data in Excel, and reopen the tool.
Generator and formula-to-value commands intentionally replace selected content.
Verify leading-zero IDs, long numbers, decimal conventions, dates, formulas, totals, and row counts.

How to use DataScrub dialogs

Dialogs share an options area, preview/data area, status footer, Apply, and Cancel. They resize, support high-DPI scaling, and expose keyboard/accessibility names. Press Esc to cancel or Ctrl+Enter to apply when enabled.

Remove Characters two-pane dialog — **Options + preview:** Apply stays disabled when no cells would change and the preview explains the next step.

Advanced Replace pairs and preview — **Multi-rule tools:** configure rules, review output, then apply. Presets make repeatable cleanup easier.

Unit Converter options and preview — **Calculation tools:** choose conversion and output behavior; formulas are protected from accidental flattening.

Start group

Command	What it does	Output and cautions
Health Scan	Scans the selection for blanks, whitespace/type inconsistencies, duplicates, formulas/errors, and other quality signals.	Read only first Select an issue before Fix Selected; issues needing choices direct you to a specialist tool.
Quick Clean	Runs a guided set of common whitespace, typography, case, and cleanup operations with one preview.	In place Review enabled steps; formulas are preserved.
Reusable Pipeline...	Builds, reorders, previews, saves, and runs a repeatable multi-step cleaning pipeline.	In place Saved pipelines store rules, not workbook data.
Undo DataScrub	Reverses the latest DataScrub values, formulas, formats, output, inserted column, or guarded deletion.	One level Return to the named workbook/sheet if prompted.

Text group

Whitespace

Command	What it does	Output and cautions
Clean All Whitespace	Trims edges, collapses repeated spaces, and removes line breaks and non-breaking spaces.	In place General cleanup for pasted/imported text.
Trim Leading & Trailing Spaces	Removes whitespace at the beginning/end while preserving internal spacing.	In place Useful before matching or deduplication.
Collapse Repeated Spaces	Converts runs of ordinary internal spaces to one.	In place Review fixed-width text.
Remove Every Space	Removes all ordinary spaces.	In place Intended for codes; words will join.
Remove Non-Breaking Spaces	Removes web/Word non-breaking spaces that break matching.	In place
Remove Line Breaks	Removes in-cell CR/LF and Alt+Enter line breaks.	In place Check whether a separating space is needed.

Letter Case

Command	What it does	Output and cautions
UPPER CASE	Converts letters to uppercase.	In place
lower case	Converts letters to lowercase.	In place
Title Case	Capitalizes words for names/headings.	In place Review acronyms, particles, and brands.
Sentence case	Uppercases the beginning and lowers remaining letters.	In place Review abbreviations and multi-sentence cells.
tOGGLE cASE	Swaps uppercase and lowercase characters.	In place

Characters & Accents

Command	What it does	Output and cautions
Remove Characters by Rule...	Removes/keeps first or last N characters, Unicode letters, digits, controls/zero-width marks, accents, or custom characters.	In place Live preview; Apply is disabled when nothing changes.
Remove All Letters	Removes Unicode alphabetic characters while retaining digits/symbols.	In place
Remove All Digits	Removes digit characters while retaining text/symbols.	In place
Remove Accents	Converts accented letters to base forms, such as é to e.	In place Language distinctions may be lost.
Remove Repeated Items Within Cells...	Deduplicates delimited items inside each cell.	In place Does not delete worksheet rows.

Order & Delimiters

Command	What it does	Output and cautions
Reverse Characters	Reverses character order in each selected value.	In place
Sort Items Within Cells...	Sorts delimited items with direction, delimiter, trim, and case choices.	In place
Change In-Cell Delimiter...	Changes comma/tab/pipe/space/custom item separators.	In place Preview literal delimiters.

Split, Join & Create

Command	What it does	Output and cautions
Add Text by Position...	Adds a prefix, suffix, or text at a character position.	In place Formulas are preserved.
Pad to Fixed Width...	Pads left/right to a target length using a chosen character.	In place Output is text.
Convert Numbers to Ordinals	Converts 1, 2, 23 to 1st, 2nd, 23rd.	In place Output is text.
Split by Delimiter...	Splits content into columns or rows by a chosen delimiter.	Output Contiguous safe range; collisions are checked.
Split at Fixed Width...	Splits text at specified widths/positions.	Output Confirm longest record and destination width.
Split at Line Breaks...	Splits multi-line cells into rows or columns.	Output Row output can be structural.
Join Selected Cells...	Concatenates cells with a chosen separator and blank/trim options.	Output Destination is collision protected.
Generate Random Text or UUIDs...	Fills selection with random strings or UUIDs.	Generate Intentionally replaces selected cells.
Insert Timestamps...	Fills current date/time using a custom format.	Generate Static values, not live formulas.
Insert Special Characters...	Inserts bullets, dashes, copyright, trademark, currency, and other symbols.	Generate
Generate Text Sequence...	Creates sequences such as A, B, C or ID-001.	Generate Check start, step, prefix, padding.

Extract & Inspect

Command	What it does	Output and cautions
Extract by Position...	Extracts first/last N or a substring at a position.	In place Formulas remain formulas.
Extract Between Markers...	Extracts text between start/end markers.	In place Choose unmatched behavior.
Keep Numeric Characters	Keeps numeric/sign/decimal characters from mixed text.	In place
Extract Email Addresses or URLs	Finds email or URL-looking substrings in longer text.	In place Does not prove existence.
Extract with Regex...	Finds, highlights, extracts, or replaces using regular expressions.	Output Multi-column output writes after the full selection.
Write Character Counts	Writes character counts for source cells.	Inserted column One contiguous column; structural Undo.
Write Word Counts	Writes estimated word counts for source cells.	Inserted column Structural Undo.
Highlight by Pattern...	Fills cells matching/not matching a regex.	Formatting Undo restores prior fill.

Numbers group

Generate & Insert

Command	What it does	Output and cautions
Sequential Numbers...	Fills a numeric sequence using start and step.	Generate Constants replace selected values.
Random Numbers...	Generates random integers/decimals in a range.	Generate Static; regenerated on each run.
Unique IDs...	Creates incrementing IDs with prefix/padding.	Generate Uniqueness is within the run.
Row Numbers...	Fills relative or absolute worksheet row numbers.	Generate
Running Total...	Calculates a cumulative total.	Output Insert/chosen destination; collision and Undo guards.
Calculated Column...	Creates results from a formula using column references.	Output Validate formula preview and destination.

Calculate & Analyze

Command	What it does	Output and cautions
Quick Calculator (Add Mode)...	Opens Quick Calculator in addition mode.	In place Non-numeric cells skipped; formulas preserved.
Add a Value...	Adds a constant to eligible numbers.	In place
Subtract a Value...	Subtracts a constant.	In place
Multiply by a Value...	Multiplies by a constant.	In place
Divide by a Value...	Divides by a nonzero constant.	In place Division by zero blocked.
Apply Markup or Discount...	Increases/decreases values by a percentage.	In place Confirm markup versus discount.
Calculate Percentage Change...	Calculates change between rows or two columns.	Output Review zero base values.
Scale or Normalize...	Rescales to 0–1, 0–100, z-score, or custom range.	In place/output Formulas preserved.
Rank Values...	Ranks ascending/descending with tie choices.	Output Review blanks/duplicates.
Spell Out Numbers...	Converts numbers to English words.	In place Text output; formulas preserved.

Convert & Fix

Command	What it does	Output and cautions
Round Numbers...	Rounds by decimal places or multiples.	In place Changes stored values.
Add or Remove Leading Zeros...	Pads to a text width or strips leading zeroes.	In place Verify IDs/postal codes.
Make Values Positive	Applies absolute value.	In place
Make Values Negative	Makes nonzero values negative.	In place
Flip Positive and Negative Signs	Multiplies values by -1.	In place
Convert Percent Values to Decimals	Divides by 100, e.g. 50 to 0.5.	In place Value and display format differ.
Convert Decimal Values to Percents	Multiplies by 100, e.g. 0.5 to 50.	In place
Convert Text to Numbers...	Parses numeric text with separator/currency choices.	In place Review IDs, long numbers, locale.
Extract Number Values from Text...	Extracts values such as 42.50 from mixed text.	In place Formulas remain formulas.
Convert Regional Number Formats...	Converts decimal/thousands conventions.	In place Verify ambiguous values.
Convert Units...	Converts supported measurement units.	In place/output Formulas preserved.
Replace Formula Errors...	Replaces checked Excel error types with blank/zero/custom text.	In place Non-target formulas stay formulas.
Replace with Displayed Text	Replaces stored values/formulas with formatted display text.	Destructive conversion Currency/date/percent become text.

Dates group

Command	What it does	Output and cautions
Convert Text to Dates...	Parses text into true Excel dates.	In place Formats only parsed cells; formulas preserved; review day/month order.
Extract Day of Week...	Writes weekday names/numbers.	Output Inserted-column Undo.
Extract Month Name...	Writes month names.	Output
Extract Year...	Writes four-digit years.	Output
Add or Subtract Time...	Adds/subtracts days, months, or years.	Output Review month ends/leap years.
Calculate Date Difference...	Calculates elapsed days/months/years or detailed differences.	Output Review inclusivity/reversed dates.
Calculate Age...	Calculates age as of today or a reference date.	Output Future/invalid dates flagged.

Cleanup group

Rows, Cells & Formulas

Command	What it does	Output and cautions
Find and Handle Duplicate Rows...	Finds full selected-column row tuples; highlight, count, select, or delete; keep first/last.	Structural if deleting Confirmed and guarded; unsafe sheets declined.
Remove Blank Rows or Columns...	Deletes fully blank rows/columns or rows blank in a chosen column.	Structural Checks full UsedRange; one-level structural Undo.
Unmerge and Fill Values	Unmerges and fills resulting cells with the original value.	Structure/values Review presentation layouts.
Fill Blank Cells...	Fills blanks down/up/left/right, constant, sequence, pattern, or interpolation.	In place Existing nonblank formulas/values retained.
Convert Formulas to Values	Freezes formulas as current calculated results.	Intentional conversion Snapshots before conversion.

Standardize & Replace

Command	What it does	Output and cautions
Clean Typography & Control Characters...	Normalizes smart typography and removes selected controls/nonprinting/zero-width characters.	In place Preview meaningful punctuation.
Remove HTML and Decode Entities	Strips tags and decodes HTML entities/URL-encoded text.	In place Not a publishing sanitizer.
Standardize Category Values...	Groups similar categories and applies a canonical value.	In place Inspect suggestions first.
Run Multi-Rule Find & Replace...	Runs ordered find/replace pairs with whole-cell/case/regex and presets.	In place Rule order matters.

Contacts group

Command	What it does	Output and cautions
Format Phone Numbers...	Normalizes and formats local/international/E.164-like numbers.	In place Confirm country/trunk assumptions; does not prove assignment.
Validate Email Formats...	Checks syntax; highlights or writes validation/domain output.	Formatting/output Does not prove delivery.
Normalize Country Names or Codes	Maps ISO 2/3-letter and common aliases to canonical names.	In place Unknown values preserved.
Split Addresses into Columns...	Parses street, city, state/region, postal, country.	Output Addresses vary; review every column.
Split Names into Columns...	Splits prefix, first, middle, last, suffix.	Output Review cultural/compound names.
Normalize Website URLs	Enforces HTTPS, normalizes host/path, removes common tracking parameters.	In place Non-web/invalid values preserved.

Import group

Command	What it does	Output and cautions
Import CSV...	Imports quoted delimiters, escaped quotes, embedded newlines, and leading-zero identifiers.	Output Verify encoding, delimiter, header, destination, IDs, dates.
Import Delimited Text...	Imports tab/pipe/comma/semicolon/custom-delimited files.	Output Shared quoted-field parser; verify malformed rows.
Import PDF Tables or Text...	Extracts text/detected tables from digital PDFs.	Output Scans require OCR; complex layouts need correction.
Import Word Tables or Text...	Extracts tables/paragraphs from .docx.	Output Convert legacy .doc; nested/merged tables may flatten.
Combine Multiple Files...	Stacks CSV/TSV/text files with header choices.	Output Confirm schemas, order, delimiter, headers.
Preview and Clean Clipboard Data...	Previews/cleans tabular clipboard text and pastes a table.	Output Destination collisions require confirmation.

Support group

Command	What it does	Output and cautions
Activate or Manage License...	Activates, displays, deactivates, or renews a license/device.	Account Internet/production service required.
View License Status	Shows trial/license state, expiration, days remaining.	Read only
Check for Updates...	Reports whether a newer trusted release exists.	Informational No silent XLL replacement.
Help & Reference	Opens searchable in-add-in feature help, version, docs, support.	Read only

Example workflows

Clean a customer/contact export

Save a copy and select the data region.
Run Health Scan.
Clean whitespace/typography.
Format phones, validate emails, normalize countries and URLs.
Split names/addresses on copied source columns and review exceptions.
Deduplicate using columns that define customer identity.
Compare row counts and spot-check before saving.

Prepare imported transaction data

Import into a blank sheet; verify IDs and locale.
Convert numbers/dates after checking ambiguity.
Repair only intended formula errors.
Add totals, percentage change, rank, or calculated output.
Reconcile totals to the source.

Build a repeatable monthly cleanup

Clean one representative file manually.
Add safe in-place steps to Reusable Pipeline in order.
Preview, save, and test on another copy.
Keep structural/import/judgment steps outside automation.

Portable mode and local files

The launcher embeds both XLL architectures, this guide, and screenshots. It verifies SHA-256 and extracts under %LOCALAPPDATA%\DataScrub\PortableRuntime\<version>. Loading is session-only. Close Excel to unload it. “Remove runtime files” preserves license/preferences.

Integrity: compare the launcher/ZIP SHA-256 with the trusted release channel. Do not use a mismatched artifact.

Limits and safeguards

Undo: one operation. Structural deletion is declined on protected sheets, tables, merged cells, and oversized UsedRanges.
PDF: digital text only; no OCR.
Contact checks: formatting cannot confirm real-world existence.
Locale: date/number strings can be ambiguous.
Large ranges: test on a copy; some operations intentionally refuse unsafe sizes.
Ribbon: Office width/scaling/language affects collapse; restart Excel after updates.

Troubleshooting

Problem	What to do
Tab missing or looks old	Close every Excel process, reopen the latest launcher, and load DataScrub. Check Trust Center if refused.
“No cells selected”	Cancel, select non-empty cells, reopen. Imports require a file/clipboard and destination.
Apply disabled	No changes, missing required input, or no eligible values. Read status/preview.
Overwrite warning	Choose a blank destination or confirm only after reviewing affected cells.
Structural deletion refused	Use an unprotected plain sheet without tables/merged cells, or reduce UsedRange.
Undo requests another sheet	Return to the named workbook/sheet; cross-workbook restore is blocked.
Import columns wrong	Recheck delimiter, quoting, encoding, headers, source type, locale.
PDF empty/garbled	Confirm selectable text; OCR scanned pages externally.
Cleanup blocked	Close all Excel windows/background processes, retry.
Activation fails	Check key, connection, clock, subscription, device allocation. Do not share keys.