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)
 
Back
Top