Find the text cells masquerading as numbers.

From time to time, an import gone awry will leave you with values that look like numbers but are actually text. They won’t total, they won’t compute in formulas and won’t be picked up by a pivot table.

If you have a whole column of them, usually a Data \ Text to columns will take care of the issue, but sometimes that’s not your best tool.

A quick way to find out which cells are not really numbers (and a quick way to find lots of other types of cell) is to use the Goto…Special function. Select the range containing the culprits, press F5 and you have the Go to menu, press the Special button and you will get all the Special Cells option. Select the appropriate one, in this case, Constants \ Text and voilà, the text cells are selected. You can now manually address the issues.

Try it, you might like it.

Thomas

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s