Changing an Excel column of text numbers to actual numbers in a couple of clicks

I have a bit of a love-hate relationship with Excel. I know it’s very powerful, but I really don’t know how to use it beyond the very basics. Today was one of those instances where I had a column of “numbers” in a spreadsheet that I wanted to sort, but Excel thought they were text strings. I wasn’t sure how to get Excel to truly treat them as numbers until I found this cool trick…

Here I have a spreadsheet with site sizes in column C. I wanted to sort them largest to smallest, but it was sorting them as text values instead of numeric values:

excel-texttonumber-20181220-1 - Copy

To change them into numeric formats, I can use the Text To Columns option (yeah, not very intuitive). I selected the entire contents of column C and clicked Data > Text To Columns:

excel-texttonumber-20181220-2 - Copy

In the wizard, don’t do anything but click Finish:

excel-texttonumber-20181220-3 - Copy

Now when I sort that column, I get a sort based on actual numeric values!

excel-texttonumber-20181220-4 - Copy

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s