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

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

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

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

Thanks Guys,

The solution works great for me.

Thanks a lot.

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