sort and clean up a list

[chouston]

anyone know how I can sort this mess...
e.g.

a101
----
aprt
a102
b103
>>>>
a103
c201
====
c203
build


I want to sort to this:

a101
a102
a103
b103
c201
c203

I don't think isnumeric will work for me...I'm really stuck with this one!

Any help greatly appreciated!

 

[Aladin Akyurek]

On 2002-05-08 11:15, chouston wrote:
anyone know how I can sort this mess...
e.g.

a101
----
aprt
a102
b103
>>>>
a103
c201
====
c203
build


I want to sort to this:

a101
a102
a103
b103
c201
c203

I don't think isnumeric will work for me...I'm really stuck with this one!

Any help greatly appreciated!

In a column next to your data, enter:

=ISNUMBER(RIGHT(A1)+0)+0

and copy down.

Then sort on the second column descending then the first column ascending.

 

[chouston]

Thanks Aladin!
I was trying to use vba to do this, but I was able to use your advice and make some progress, I'm able to identify the cells I want using the following code, but am having trouble deleting the row. Perhaps you (or anyone) might be able to suggest the proper syntax:

cleanrange = ActiveWorkbook.Sheets("data").Range(Cells(1, 1), Cells(4000, 1))

For Each cel In cleanrange
aptunit = Right(cel, 3)
If IsNumeric(aptunit) = False Then
ActiveWorkbook.Sheets("data").Range(cel).EntireRow.Delete
End If
Next cel

Many thanks

 

[donh]

If all the data is in one column and is how you list then try this:

if there is not a title row, insert one

copy the entire column to another column
data/text to columns/fixed with
insert a break line after the first character only and remove any others

Highlight the entire range
data/filter/auto filter

in the third column should now only be numbers or characters

click on the dropdown, custom, is greater than 0

copy this, paste it into another workbook, and delete any unnecessary columns

 

[chouston]

Could someone suggest vba function or syntax I can use to delete the entirerow if not numeric?

--
cleanrange = ActiveWorkbook.Sheets("data").Range(Cells(1, 1), Cells(4000, 1))

For Each cel In cleanrange
aptunit = Right(cel, 3)
If IsNumeric(aptunit) = False Then
====> "I want to delete this row!!!"
End If
Next cel
--

Thanks