Convert numbers saved as text to real numbers

In the world of Excel, what you see isn’t always what you get. A cell might look like a number, but secretly be formatted as text. This happens because Excel can assign different data types to visually identical information.

Sometimes this is useful, but other times it causes major office headaches.

The most common complaint? Data that won’t sum properly. But that’s just the beginning. Here are some real office nightmares caused by numbers behaving as text:

1. Calculation Catastrophes: Imagine a financial analyst working on a complex model. Unknown to them, some “numbers” are actually text. Formulas depending on these values calculate incorrectly, leading to wrong results and bad business decisions.
2. Sorting Chaos: A sales report looks perfect at first glance. But some revenue figures are text. When sorted, these entries land in the wrong place, completely scrambling the report’s order.
3. Graphical Distortions: A project manager creates a chart to track progress. If some data points are text, the chart paints a completely misleading picture of reality.
4. Import Failures: A marketing team imports customer data. Some phone numbers come in as text. When they try to use these for campaigns, everything fails because Excel treats them as words, not digits.
5. Formatting Confusion: An HR database shows employee tenure. If some entries are text, conditional formatting based on numerical ranges gives completely wrong visual cues.

Why Numbers Become Text

Numbers get saved as text for several common reasons:

1. Data imported from other systems (databases, web reports, etc.) that export everything as text by default.
2. Numbers intentionally saved as text using an apostrophe (‘) before them.
3. Mismatched decimal separators when copying data. Some systems use periods, others commas. If it doesn’t match Excel’s settings, decimals become text.
4. Hidden characters like extra spaces, tabs, or line breaks before or after numbers. These are invisible to you but tell Excel “this is text!”
5. Thousands separators using non-standard spaces (common in data exports).
6. Using the “import data” feature and accidentally setting columns to text format.

How to Save Numbers as Text (On Purpose)

The quickest way? Type an apostrophe before your number. This forces Excel to treat it as text.

But what if you have hundreds of cells? Check out our guide on adding characters to multiple cells at once.

How to Spot Text Disguised as Numbers

Excel’s Built-in Warning System

Microsoft added visual clues for this exact problem. Look for a small green triangle in the corner of cells. Select the cell, and a warning appears:

Excel usually offers to convert these to numbers with one click:

But this doesn’t always work. When Excel misses the problem, try these methods.

The Alignment Test

Here’s the easiest visual clue: text aligns left by default, numbers align right. Don’t change the default alignment – it’s your quickest way to spot impostor numbers!

Data Validation for Prevention

If you create files that others fill out, use Data Validation to lock cells to numbers only. Here’s how:

• Select the cells you want to protect
• Choose whether you need whole numbers or decimals
• Set your comparison type and value ranges
• Click OK

Warning: This won’t stop someone from pasting text over your validated cells, which overwrites the rules.

Fixing the Problem

Here are your quick-fix solutions based on what’s causing the issue.

Extra Spaces

If spaces are the culprit, you need to remove extra spaces from cells.

Wrong Thousands Separator

When system separators don’t match your data:

1. Use Find and Replace to swap separators (like period to comma)
2. Change the separator in Excel’s settings (File → Options → Advanced)

Other Hidden Characters

If cells contain invisible characters mixed with numbers:

1. If you can see the extra characters, use Find and Replace to remove them
2. For multiple hidden characters (tabs, line breaks, etc.), don’t waste time deleting – instead, extract just the numbers from text cells

The One-Click Solution with !SEMTools (Free)

Sometimes you don’t want to play detective. You just want your numbers to work like numbers. What if you could fix this with one click?

That’s where my Excel add-in comes to the rescue. Install !SEMTools once, and it loads every time you open Excel. The intuitive tool panel includes a magic button that converts text to numbers instantly.

This feature is completely free – no limitations, no upgrades needed. Find it in the “CHANGE” menu under “Change Numbers.”

This post is also available in RU.