Converting text to numbers (easily) in bulk

On many occasions, I export out reports in Excel. Maybe you do too. Depending on the platform, I get “numbers” are are actually text. You can’t total or really do any work with them in this text form.

Here is a small piece of a much larger document:

The “$” is actually part of the text. We need to remove it. If you only have a few numbers, the tedious way is to go into each cell and edit. Excel will know these are number and then format that way.

But what if you have hundreds? There are two ways that work in this instance. One, do Ctrl H. Search and replace the “$” with an empty field.

Excel will then convert the fields to numbers.

Or…

Off to the side type the number 1. Then format that number however you like. I like currency. Now copy the cell with the number 1 in it.

Here is where the magic happens.

You want to:

  1. Select the area with the text numbers.
  2. Paste/Special
  3. Multiply

Now you have numbers instead of text.

Leave a Reply

Your email address will not be published. Required fields are marked *