Renumbering Col A

[pcor]

I have app 1500 numbers in col A.some as single digits,others are in the
ten's or hundred's or thousand's such as 1,12,123,4567 etc
I want a maccro that will do as follows:
If col a1 = 1 I want it to look like this "2007 0001"
If col a 1 =12 I wnat it to look like this "2007 0012"
If col A=123, I want it to look like this"2007 0123"
the highest number I have is a four digit number (9999) and that should look
like thsi"2007 9999"
Thanks for any help

 

[carlo]

Hi pcor

you could format your cells like this:
custom format: "2007" 0000
or
you could add a new temporary column B and enter this formula into B1:
="2007" & right("0000" & A1,4)
and then copy it down, by doubleclicking on the handler (unless you
have blank rows, then you should drag the handler)
Copy all the cells, select A1 and right click, "paste special...",
select values, ok.
Delete Column B

Difference between first and second option:
with the first option you still have direct access to your basic
number, because the value of the field is still 1, 12, 123 or 1234.
But if you need to use the term "2007 xxxx" somewhere else i would
recommend option 2.

Hth
Carlo

 

[pcor]

Worked great thanks

Hi pcor

you could format your cells like this:
custom format: "2007" 0000
or
you could add a new temporary column B and enter this formula into B1:
="2007" & right("0000" & A1,4)
and then copy it down, by doubleclicking on the handler (unless you
have blank rows, then you should drag the handler)
Copy all the cells, select A1 and right click, "paste special...",
select values, ok.
Delete Column B

Difference between first and second option:
with the first option you still have direct access to your basic
number, because the value of the field is still 1, 12, 123 or 1234.
But if you need to use the term "2007 xxxx" somewhere else i would
recommend option 2.

Hth
Carlo