MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Convert text to numeric for sorting purposes

Posted by Jenny on March 27, 2001 9:52 AM

I am importing data from a SQL database into Excel. I have a column of numbers that come in as text which we would like to sort by. I tried manipulating the column properties to make the data numeric, but it does not change existing data, only formats new data.
I would like to solve this problem without having to use VBA or any "coding".

Posted by Mark W. on March 27, 2001 10:07 AM

Jenny, what's the format applied to the column
containing your numbers?

Posted by mseyf on March 27, 2001 10:19 AM

highlight your numbers, then from the Data menu select 'Text to Columns...' and then click finish and your text SHOULD be converted



Posted by Dave Hawley on March 27, 2001 1:18 PM

Hi Jenny

Here are a couple more ways to convert text numbers to real numbers. Just make sure the column is NOT formatted as Text!

1. Put =Value(A1) in any cell and copy down.
Then copy the formulas and PasteSpecial as Values over the top of your text numbers.

2. Type the number 1 in any cell then copy it and highlight your text numbers and PasteSpecial-Multiply over the top.

OzGrid Business Applications