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

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.
--
SgtBob

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

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

"Bob" wrote in message
...
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.
--
SgtBob

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

=LEFT(A1,FIND("-",A1)-1) & "-" & TEXT(MID(A1,FIND("-",A1)+1,10),"00")

If this post helps click Yes
---------------
Jacob Skaria


"Bob" wrote:

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.
--
SgtBob

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

Here's another formula to try out...

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

--
Rick (MVP - Excel)


"Bob" wrote in message
...
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.
--
SgtBob