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

  • Thread starter Thread starter Bob
  • Start date Start date
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.
 
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
 
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

bug in excel 2007 for sorting function? 3
How do I write formula to check a range of cells? 5
Need Help in worksheet functions 1
adding a number to every change in value 3
Function, array, query What do I need? 1
how i convert "100" to "hundred"( number to text) in excel-2007 1
Auto re-numbering when a row is deleted 3
How do I convert a number like 19660124 to 01-24-1966 in excel? 2

Back
Top