Adding zeroes without formatting

**jarleram** · 12-16-2009, 02:17 PM

Hi! I'm working on a macro, where some of the action has to do with adding zeroes in front of numbers, so that every number gets the same amount of digits. 4 to be exact. This means that the number 1 is transformed to 0001. 12 -> 0012, and of course 123 will be 0123. You get the point. Every number gets 4 digits, and 0 is the filler.

I am a total newbie when it comes to these things, so my script consists of shameless cutting and pasting from other peoples works. This also means that I don't have the slightest clue of what to do when things aren't going my way.

Everything is going perfectly, except for one important thing: The macro treats numbers with decimals in a way I absolutely don't want it to. I want for example 12,3 to become 0012,3 -- but the macro insists on not changing it at all. I figured this happens because the comma (and the numbers behind it) are counted too. Therefore, a number like 12,3 already has four digits. But I want it to be 0012,3! This is making my head hurt.

My question is: Are there any way of making the program ignore the comma and all the other digits behind it? So that they are not counted. Or is there another way of dealing with this problem? Most of my numbers are without decimals, but the ones who do have them are causing severe damage to my nerves.

All help is tremendously appreciated!

(I don't know if I have explained myself good enough, but I hope I have. I'm from Norway, and my English isn't top notch.)

If you want to take a look at the code, here it is:

Please Login or Register  to view this content.

**solnajeff** · 12-16-2009, 02:33 PM

Hi

If you only have one decimal place then you could use

Please Login or Register  to view this content.

This would give you leading zeros and one decimal place, hope that is of some use to you.

Regards

Jeff

**jarleram** · 12-16-2009, 02:43 PM

Thanks, but that doesn't really solve the problem. I want to avoid any unnecessary decimals. That is the problem with cell formatting to.

**Marzuk** · 12-16-2009, 02:51 PM

If we are just padding numbers that will *always* be a 4 digit number after padding, perhaps a simple formula would be better?

Please Login or Register  to view this content.

This would pad any number from 1 to 999 with the appropriate amount of leading zeros, and preserve the format given that field is going to be a text field. Keeping in mind this is not a complete solution, only suggestive that going another direction may be easier / more functional.

**Jorozco** · 12-16-2009, 02:52 PM

Try "0000.#" instead of the string above. That should work.

**jarleram** · 12-16-2009, 03:02 PM

Marzuk: That formula didn't work with my Excel (2003). But what would happen with a larger number? Like 1000?

Jorozco: That leaves an extra comma for every number without a decimal. Plus, it would not work with more than one decimal.

**Marzuk** · 12-16-2009, 03:07 PM

Should work without issue. If the number was 1000, it would just return 1000. If it was over 1000 it would truncate it. Keeping in mind you would need to change the cell reference (I have b2 selected here).

**Jorozco** · 12-16-2009, 03:35 PM

You may have to do an if function to remove the period. The way to keep decimals is to enter "0000.##############".

**jarleram** · 12-17-2009, 03:38 AM

Marzuk: I just can't get it to work. I get function error when I try it.

Jorozco: Can you help me we with an if function? I am, like I said, a total newbie.

Here is one example of the function that I am using:

="DIN 635-2 " & "Ø" & B1 & "/" & C1 & "x" & D1 & " tapered"

This this is to create a list of products. The B, C and D cells must have 4 digits, so that I get for example "DIN 635-2 Ø1000/0500x0025 tapered".

Is there another way around it?

**DonkeyOte** · 12-17-2009, 03:54 AM

I'm not setup at present with a format with comma for decimal delimiter but you could just split your string based on the comma but going back to your original post:

Please Login or Register  to view this content.

**jarleram** · 12-17-2009, 04:13 AM

DonkeyOte: Thanks, I was hoping that would work, but I think I may have inserted it in the wrong place? I get: "compile error: Duplicate declaration in current scope"

Here's the code:

Please Login or Register  to view this content.

**DonkeyOte** · 12-17-2009, 04:20 AM

The code provided was to replace your code in it's entirety rather than to work in conjunction with it.

Please Login or Register  to view this content.

**jarleram** · 12-17-2009, 04:24 AM

Thank you so much!! That is working perfectly!

Adding zeroes without formatting

LinkBack

Thread Tools

Rate This Thread

Display

Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Re: Adding zeroes without formatting

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions