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?!?!?!?!?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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,458
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

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