Sort text Values in Numeric Order

  • Thread starter Thread starter Karl Burrows
  • Start date Start date
K

Karl Burrows

I changed a field type in Access from Number to Text to allow 1a, 1b, etc.
and now when the data is imported into my Excel workbooks, the order is
still sorting as text (1, 10, 100, 101, 2, 20, etc.). Any thought son how
to get it to sort correctly now?

Thanks!
 
Karl,

IN an adjacent column add this formula

=--A1

copy down and then sort by the helper column.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Anything that has a text value (1a, 1b, 1c), gives me a #VALUE error and it
still wants to sort as a text value. Any other suggestions?

Thanks!

Karl,

IN an adjacent column add this formula

=--A1

copy down and then sort by the helper column.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
If you separate your values into two helper cells--one for the numeric portion
and one for the text portion, you can sort your data based on those two helper
columns.

Depending on what your data looks like, you could use Data|Text to columns or
formulas to parse those values.

You may want to post a representative sample of what your data looks like.
 
Here is what I ended up doing:

Added a function to my VBA module:
Function CellValue(c) As Double
CellValue = Val(c)
End FunctionThen created a column using =CellValue(A1) and copied down
for all the values in the column and then sorted by this row first, then the
original column of data.This seemed to work for what I wanted. Thanks for
your help!!!
If you separate your values into two helper cells--one for the numeric
portion
and one for the text portion, you can sort your data based on those two
helper
columns.

Depending on what your data looks like, you could use Data|Text to columns
or
formulas to parse those values.

You may want to post a representative sample of what your data looks like.
 
Back
Top