Quote:
Originally Posted by drop2d
Bat signal for rconverse....
Any idea how I can format a number in vba to have leading 0's? For example, I have employee Id numbers that go from:
1
99999999
I want all of them to be formatted at 8 characters so they would show as:
00000001
00000002
00000003
etc.
I'm going blind searching the interwebs and trying different shit that just isn't working. It's probably something simple but after 12 hours of work, I'm spent. Any help would be appreciated!!
|
Not for nothing but please don't research for that much time unless completely necessary. The bold portion of your post is like nails on a chalkboard in my "Excel World."
I have a lot of instances where I need to maintain leading zeroes and the REPT function always seems to do the trick.
I"m not exactly sure how your macro is setup but here is how the REPT function works:
Assuming your data is in column B and you have a header row of 1, meaning your data starts in row 2. You could insert this column in column A.
=REPT(0, 8-LEN(B2)) & B2
This formula will ensure that all IDs have a length of 8, regardless of leading zeroes or not. If the ID is a length of 8 characters, there will be no zeroes added.
So let's say B2 = 1, this will return 00000001
So let's say B3 = 111, this will return 00000111
So let's say B4 = 1111111, this will return 1111111
If you need help inserting that into VBA, please let me know. Happy to help.
Quote:
Originally Posted by Climb2safety
Does this result in two distinct versions of excel now running, independently one with each spreadsheet? Or just a way to manipulate both inside one version?
|
It will literally take how many instance of Excel you have open and align them all vertically. If you have four different spreadsheets open, you'll see "squished" versions of all 4 spreadsheets. There's also the option to sync scrolling if you go this route, which is nice if you're comparing two spreadsheets (like this week to last week or test to prod). Literally just depends on what you're trying to accomplish.
Quote:
Originally Posted by drop2d
Thanks... I do know how to do that. I’m looking for a way to do it in vba so I can process it in the giant macro I’m already running on the data. I have a customer changing systems and the data they are sending isn’t formatted the same (old system had 8 characters). I’m sure it’s something relatively easy... my brain is just fried for today
|
Not "relatively" easy, buddy. Just easy, easy.
Give your brain a rest and enjoy your weekend!!