How to convert numbers stored as text without corrupting values
Convert numeric text by first identifying syntax families and units, then parsing into a candidate column with a rule for each family. Preserve the raw value, list unsupported strings, and reconcile counts and totals. A blanket VALUE or replace operation can confuse decimal commas, percentages, parentheses, and currency symbols.
The decisions that matter
- Group raw strings by currency, separators, sign notation, percentage, and exception tokens.
- Retain currency or percentage semantics in its own field before parsing magnitude.
- Use one locale-aware rule per syntax family and keep unsupported values explicit.
- Compare parsed counts, nulls, min/max, and totals with a manually verified sample.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| VALUE/number conversion | One known locale | Errors and percentage magnitude |
| Find and replace | One controlled character cleanup | Decimal/thousands ambiguity |
| Family-based parser | Mixed locale and currency data | Source metadata and exceptions |
Where the difficulty begins
The same column may contain $1,250.00, €1.250,00, (340.50), 12%, blanks, and words such as pending. Automatic conversion can change magnitude or silently create errors.
What may be distorting the answer
- Sums ignore values that look numeric.
- Replacing commas changes 1,25 into 125.
- Percentages become 12 instead of 0.12 or vice versa.
From the field · Bookkeeper
Clean supplier invoice amounts from three countries
Invoices from the US, Germany, and India use different symbols and separators. Negative credits appear in parentheses, and a discount field alternates between 8% and 0.08.
Columns used in the example
invoice_id · amount_raw · currency · discount_raw · country · invoice_date
Follow it with your own file
- Profile distinct syntax patterns by country and currency.
- Parse amount_raw with the appropriate thousands and decimal rule.
- make consistent discount to one documented decimal representation.
- Place pending, malformed, and conflicting values in an exception set.
Check: Every nonblank raw value must be parsed or explicitly excepted. check totals against the source separately by currency before any conversion and spot-check negative credits and percentages.
A numeric value is magnitude plus unit and convention
Parsing succeeds only when the workflow knows what separators, symbols, parentheses, and percentages mean. Keeping currency and unit fields visible prevents a syntactically successful conversion from combining incomparable amounts.
Where this usually goes wrong
A comma can be a decimal or thousands separator. Do not infer its meaning from one value such as 1,250; use source locale, currency, surrounding values, and explicit metadata.
Build the result step by step
- Inventory formats: Group raw strings by currency, separators, sign notation, percentage, and exception tokens.
- Separate unit from value: Retain currency or percentage semantics in its own field before parsing magnitude.
- Parse candidates: Use one locale-aware rule per syntax family and keep unsupported values explicit.
- Reconcile: Compare parsed counts, nulls, min/max, and totals with a manually verified sample.
Check the source before you publish
- Syntax families inventoried
- Units retained
- Candidate column used
- Unsupported values listed
- Per-unit totals reconciled
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Convert numbers stored as text while preserving currency and decimal meaning. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Data profiling · Reviewable cleaning · Reusable workflows · Version history
A numeric column that can be safely summed and analyzed while every original string, unit, and conversion exception remains traceable.
Questions readers usually ask next
Why are numbers stored as text in Excel?
Imports preserve symbols, separators, apostrophes, or mixed values that prevent one numeric type. Source systems may also export display strings.
Can I remove every comma before converting?
No. In many locales the comma is the decimal separator. Determine syntax family and locale before changing it.