Making a 7 digit number into an 8 digit one!!

[Myke]

Hi,

I have a whole colum of figures in column H of which some are correct and some are not.

Basically I need a little macro/function which looks for any numbers in column H which are 7 digits long and puts a "0" in front of them. However, if the number is 8 digits long then it does nothing (as it SHOULD be 8 digits). I have already changed the format to Text Only (otherwise I am aware that the 0 will disappear again.

Help?!?!?!?!?

 

[fairwinds]

Hi,

Try:

=RIGHT(0&A1,8)

 

[Myke]

Fab. Worked a treat. Now I'll just tell the boss it'll take me couple of hours to fix and I'm sorted for a good skive !!!

 

[Brian from Maui]

Try,

=IF(LEN(A1)<8,"0"&A1,A1)

 

[Myke]

Okay that worked but its didnt get me towards my final goal. Guess I'm not going to get to go home early after all. Basically I'm now left with my 8 digit numbers (which is what I wanted) but I cant convert them to date format. i.e. I now have 01011999 but what I need is 01/01/1999. Where am I going wrong now??? I'm too dense to sort this out myself and our IT departments line is "We don't support Microsoft Excel!!" (like THAT really helps me)


help.... again!! ray:

 

[Brian from Maui]

Okay that worked but its didnt get me towards my final goal. Guess I'm not going to get to go home early after all. Basically I'm now left with my 8 digit numbers (which is what I wanted) but I cant convert them to date format. i.e. I now have 01011999 but what I need is 01/01/1999. Where am I going wrong now??? I'm too dense to sort this out myself and our IT departments line is "We don't support Microsoft Excel!!" (like THAT really helps me)


help.... again!! ray:

Try going to Data/Text to Columns and follow the Wizard from step 3. Then format your range to display mm/dd/yyyy

 

[fairwinds]

...or formula:

=DATE(RIGHT(A1,4),MID(RIGHT(0&A1,8),3,2),LEFT(RIGHT(0&A1,8),2))

 

[Myke]

YAY!!!! It worked. Myke can go home now and feel very clever and important to the boss!!! You guys all have my undying appreciation!!!!