Sort text Values in Numeric Order

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)


"Karl Burrows" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
>

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!

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
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)


"Karl Burrows" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
>

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.

Karl Burrows wrote:
>
> 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!
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> 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)
>
> "Karl Burrows" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
> >

--

Dave Peterson

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!!!
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
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.

Karl Burrows wrote:
>
> 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!
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> 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)
>
> "Karl Burrows" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
> >

--

Dave Peterson