formating numbers

[Guest]

i have a huge list of numbers that appear as 12345-1, but would like to
change them so they appear as 0012345-001. help please

 

[Bob Phillips]

In an adjacent cell add this formula and then copy down

=RIGHT("00000000"&LEFT(A1,FIND("-",A1)),9)&RIGHT("000"&MID(A1,FIND("-",A1)+1
,99),3)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi

Either
=TEXT(LEFT(A1,5),"0000000")&"-"&TEXT(RIGHT(A1),"000")
to have the values as text.
Or, pre format the cells in the column that is to take the new values with
Format>Cells>Number>Custom> 0000000-000
then
=--LEFT(A1,5)*1000+--RIGHT(A1)
to keep it as a number

If your existing values are not in column A, change as appropriate.
Drag the formula down the receiving column as far as required.

Regards

Roger Govier

 

[Dave Peterson]

I think that the *1000 and + will be sufficient to coerce the text into numbers:

=LEFT(A1,5)*1000+RIGHT(A1)

Still with your custom format of: 0000000-000

 

[Roger Govier]

Quite right Dave, thanks. They are superfluous in this case.

Regards

Roger Govier