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

HTH

Mark

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.

Dave
OzGrid Business Applications