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

Myke

New Member
Joined
Jan 23, 2003
Messages
39
:rolleyes:
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?!?!?!?!?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Myke

New Member
Joined
Jan 23, 2003
Messages
39
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 !!!
 

Myke

New Member
Joined
Jan 23, 2003
Messages
39

ADVERTISEMENT

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. :cry: 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!! :pray:
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Myke said:
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. :cry: 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!! :pray:

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

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
...or formula:

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

Myke

New Member
Joined
Jan 23, 2003
Messages
39
YAY!!!! It worked. Myke can go home now and feel very clever and important to the boss!!! You guys all have my undying appreciation!!!! :coffee:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,025
Messages
5,856,898
Members
431,837
Latest member
megantang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top