How do I convert a number in Excel such as 1001-1 to 1001-01?

B

Bob

I have several hundred numbers in my Excel file that need to be converted as
noted in the subject. The sorting is awkward in that 1001-11 sorts before
1001-2 and is obviously out of order- at least for my purposes.
 
F

Fred Smith

These aren't numbers to Excel, they're text. So you do something like this:
=if(len(a1)=6,left(a1,5)&"0"&right(a1,1),a1)

Regards,
Fred
 
R

Rick Rothstein

Here's another formula to try out...

=IF(MID(A1,LEN(A1)-1,1)="-",SUBSTITUTE(A1,"-","-0"),A1)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert numbers stored as text to numbers in excel 2008 3
bug in excel 2007 for sorting function? 3
How do I write formula to check a range of cells? 5
how do i count cells with values in a certain range ? 3
Function in Excel to count and list number of entries of text valuesin a row. 2
how i convert "100" to "hundred"( number to text) in excel-2007 1
How do I convert a number like 19660124 to 01-24-1966 in excel? 2
how do I convert an imported number to text 1

Top