Not sure if anyone is super familiar with Excel 2013 yet, but i'm having issues sorting a specific set of numbers.

112a
113
112
113a
114
111

I want this to sort by # to look like this

111
112
112a
113
113a
114

However all the methods i've used that worked in Excel 2010 are sorting all the numbers with letters either at the top or bottom. Wondering if anyone can help me out with the sort.

make the column all varchars and it should work. alternatively split the letter into another column then sort and have a 3rd column concatenate them together

I'll try the var chat idea, thanks. However can't split the number/letters as it is an import of 100+ account numbers.

use the text function to return the values on separate column, they are no longer recognized as pure values. then you can sort A to Z.

=TEXT(G3,"###")

This post was edited by Legolas_RH on Aug 19 2013 07:41pm

what does that have to do with anything? there's a function to split, i'm not suggesting you manually do it row by row by hand

Did you figure this out?

if you have numbers of varying numbers of digits, you could make a custom excel formula to go through each character of a string until IsNumber = true. Keep track of the characters passed and do a substring to pull that part of the string out. Use that formula (or do a macro) to create another column on numbers with the end letter removed and sort the original column according to this one

also, if there are only a few different non-numeric characters, you can make columns of
Search(string, 'a') Search(string, 'b')

Each column will return the location of the non-numeric character in the number. Then make a column of the smallest of these locations that's not zero
min(if(search > 0, a column, 9999), if(search>0, b column, 9999))

with that, you can use a function like Left(string, min location), or something like Left im not sure off the top of my head, but it'll pull out the number hopefully :P. I can do it for ya on a google spreadsheet if you'd like