Sorting number as text
 

I have a worksheet containing the PO order information. Column A is
storing the PO number which is usually a number but sometimes includes
characters, like

123
123R
246
156

I want the sorting result accroding to the ASCII order, like
123
123R
156
246

But the result is like
123
156
246
123R

I just learnt that I should format the cells with Number As Text before
filling in the value. As there are a lot of data already entered, is
there any workaround to overcome the situation?

I had tried to create another dummy column and format it with Number as
Text. Then copy the PO Number Column to that dummy column with Paste
Specail - Value. The sorting wrong is still wrong.

I had also tried to add the character ` to the beginning of each cell
value. The sorting is then correct, but it makes my application
extremely slow.
(My Application is just "FIND" a particular PO, recall the row data
into a user form, modify the data and save it back to that row).


Thanks


--
boris_lui
------------------------------------------------------------------------
boris_lui's Profile: http://www.excelforum.com/member.php...o&userid=28489
View this thread: http://www.excelforum.com/showthread...hreadid=481714

Sorting number as text
 

With cells formatted as General, you can use the FIXED function to convert
the number only cells to text:
=FIXED(A1,0,TRUE) (no decimal and no commas). Then Copy/Paste Special/
Values, this result over the original cells. Leave the cells that have text
in them alone.

Mike F
"boris_lui" wrote
in message ...

I have a worksheet containing the PO order information. Column A is
storing the PO number which is usually a number but sometimes includes
characters, like

123
123R
246
156

I want the sorting result accroding to the ASCII order, like
123
123R
156
246

But the result is like
123
156
246
123R

I just learnt that I should format the cells with Number As Text before
filling in the value. As there are a lot of data already entered, is
there any workaround to overcome the situation?

I had tried to create another dummy column and format it with Number as
Text. Then copy the PO Number Column to that dummy column with Paste
Specail - Value. The sorting wrong is still wrong.

I had also tried to add the character ` to the beginning of each cell
value. The sorting is then correct, but it makes my application
extremely slow.
(My Application is just "FIND" a particular PO, recall the row data
into a user form, modify the data and save it back to that row).


Thanks


--
boris_lui
------------------------------------------------------------------------
boris_lui's Profile:
http://www.excelforum.com/member.php...o&userid=28489
View this thread: http://www.excelforum.com/showthread...hreadid=481714

Sorting number as text
 

If it's always 3 numbers with an optional alpha character, maybe you could just
use a helper column:

=""&a1

And drag down.

Then everything will be text.



boris_lui wrote:

I have a worksheet containing the PO order information. Column A is
storing the PO number which is usually a number but sometimes includes
characters, like

123
123R
246
156

I want the sorting result accroding to the ASCII order, like
123
123R
156
246

But the result is like
123
156
246
123R

I just learnt that I should format the cells with Number As Text before
filling in the value. As there are a lot of data already entered, is
there any workaround to overcome the situation?

I had tried to create another dummy column and format it with Number as
Text. Then copy the PO Number Column to that dummy column with Paste
Specail - Value. The sorting wrong is still wrong.

I had also tried to add the character ` to the beginning of each cell
value. The sorting is then correct, but it makes my application
extremely slow.
(My Application is just "FIND" a particular PO, recall the row data
into a user form, modify the data and save it back to that row).

Thanks

--
boris_lui
------------------------------------------------------------------------
boris_lui's Profile: http://www.excelforum.com/member.php...o&userid=28489
View this thread: http://www.excelforum.com/showthread...hreadid=481714

--

Dave Peterson

Sorting number as text
 

In case you have a great number of these to do, here is some code that will
convert column A for ASCII sorting. Modify as needed for your column.

Sub NumToText()
Dim LRow As Long
Dim rng As Range
Dim c As Range
Dim OldVal As Variant

LRow = Cells(Rows.Count, "A").End(xlUp).Row 'modify as needed
Set rng = Range("A1:A" & LRow) 'modify as needed
For Each c In rng
OldVal = c.Value
If IsNumeric(c.Value) = True Then
c.Formula = "=FIXED(" & OldVal & ",0,TRUE)"
Else
'If text, do nothing
End If
Next c
rng.Copy
rng.PasteSpecial (xlValues)
Application.CutCopyMode = False
End Sub

Once this has ran, you can do your sort. It can be added to the code, but I
didn't know what all the sort criteria was.

Mike F
"boris_lui" wrote
in message ...

I have a worksheet containing the PO order information. Column A is
storing the PO number which is usually a number but sometimes includes
characters, like

123
123R
246
156

I want the sorting result accroding to the ASCII order, like
123
123R
156
246

But the result is like
123
156
246
123R

I just learnt that I should format the cells with Number As Text before
filling in the value. As there are a lot of data already entered, is
there any workaround to overcome the situation?

I had tried to create another dummy column and format it with Number as
Text. Then copy the PO Number Column to that dummy column with Paste
Specail - Value. The sorting wrong is still wrong.

I had also tried to add the character ` to the beginning of each cell
value. The sorting is then correct, but it makes my application
extremely slow.
(My Application is just "FIND" a particular PO, recall the row data
into a user form, modify the data and save it back to that row).


Thanks


--
boris_lui
------------------------------------------------------------------------
boris_lui's Profile:
http://www.excelforum.com/member.php...o&userid=28489
View this thread: http://www.excelforum.com/showthread...hreadid=481714

Sorting number as text
 

Thanks Guys,

The solution works great for me.

Thanks a lot.


--
boris_lui
------------------------------------------------------------------------
boris_lui's Profile: http://www.excelforum.com/member.php...o&userid=28489
View this thread: http://www.excelforum.com/showthread...hreadid=481714

Sorting number as text
 

Dears,

One small question follows: after changing the number in Column A into
Text, the response of writing back the data from the user form to the
worksheet becomes slow.

My worksheet contains about 55 columns. A user form reads each cell
value of particular row to the textboxes. There is a save button to
write textboxes value back to the row using the code like:

Working_Order.Cells(1, 1) = TEXTBOX1.Value
Working_Order.Cells(1, 2) = TEXTBOX2.Value
...
..

where Working_Order is a Range of that row.

Before changing the column A into Text, I got instant response after I
click the Save button.

However, after changing the Column A into Text, I have to wait for
about 5~7 seconds to get back the control of userform after the Save
button is clicked.

Any idea what cause such delay?


Thanks


--
boris_lui
------------------------------------------------------------------------
boris_lui's Profile: http://www.excelforum.com/member.php...o&userid=28489
View this thread: http://www.excelforum.com/showthread...hreadid=481714