D
Daveed
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
change them so they appear as 0012345-001. help please
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
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)
=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)
R
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
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
D
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
=LEFT(A1,5)*1000+RIGHT(A1)
Still with your custom format of: 0000000-000
R
Roger Govier
Quite right Dave, thanks. They are superfluous in this case.
Regards
Roger Govier
Regards
Roger Govier