Sort by value instead of alphabetically

[brutusmc99]

Hi, I'm grabbing some data from the Thinkorswim TD Ameritrade trading platform into a table and its in a percentage form with a plus or minus in front. When I try to sort, I am only able to sort it alphabetically for some reason. There is no option to sort numerically. Tried changing the column into general, number, and percentage and I get the same result. Anything I can do to get it to sort numerically? Thanks.

 

[brutusmc99]

Yes, I can sort the pasted column numerically. I can try to upload a file, but maybe not until tomorrow. If I don't find another workaround before then, I'll do so. Thanks for your help.

I found a workaround, I added +0 to the original formula grabbing the data from the source and it converted it automatically into numerical form. Thanks again.

 

[brutusmc99]

Ok, in the mean time you can try this:
Data in col A start at row2, result in col b:

Sub a5746846a()
Dim i As Long
Dim va

va = Range("A2", Cells(Rows.Count, "A").End(xlUp))
For i = 1 To UBound(va, 1)
    va(i, 1) = (va(i, 1)) + 0
Next

Range("b2").Resize(UBound(va, 1), 1) = va

Thank you for your help. Really appreciate it. Actually, I think I solved it, I added a +0 to the end of the equation grabbing the data from the source and it automatically converted it into numerical form.

 

[Akuini]

Glad you figured it out.

 

[Joe4]

I found a workaround, I added +0 to the original formula grabbing the data from the source and it converted it automatically into numerical form. Thanks again.

That means that the values are coming over as Text, regardless of how the column is formatted (not that the format of the column does NOT necessarily indicate how the data is being entered).
Adding a zero coerces the text values to numeric values.
If you are unable to update the original column because it is linked from another source (so you could really not apply "Text to Columns" on it), the method you used is a popular workaround.

 

[brutusmc99]

That means that the values are coming over as Text, regardless of how the column is formatted (not that the format of the column does NOT necessarily indicate how the data is being entered).
Adding a zero coerces the text values to numeric values.
If you are unable to update the original column because it is linked from another source (so you could really not apply "Text to Columns" on it), the method you used is a popular workaround.

Yeah, it was definitely coming in as text. If I manually removed a plus or minus from one of the cells, that value shifted to the right side instead of the left. When I tried the Text to Columns, though, it would just replace the value with the formula spread across several cells, so I may have been doing something wrong and maybe that approach would have worked. But the +0 approach is working, so I'll go with that for now. Thanks for your help. I really appreciate it.

 

[Joe4]

Yeah, it was definitely coming in as text. If I manually removed a plus or minus from one of the cells, that value shifted to the right side instead of the left. When I tried the Text to Columns, though, it would just replace the value with the formula spread across several cells, so I may have been doing something wrong and maybe that approach would have worked. But the +0 approach is working, so I'll go with that for now. Thanks for your help. I really appreciate it.

Yes, the Text to Columns technique would only work on hard-coded data.
It wasn't clear to me initially that you were working with formulas, and not hard-coded values.
I thought you were copy/pasting or importing the values.

 

[brutusmc99]

Yes, the Text to Columns technique would only work on hard-coded data.
It wasn't clear to me initially that you were working with formulas, and not hard-coded values.
I thought you were copy/pasting or importing the values.

It was being imported, but through a formula. The Text to Columns method will definitely come in handy for me in the future, so I appreciate the tip.