A few cells with 3 digits, I want 4 digits: 345 -> 0345

[Dinictus]

Hi all,

I have about a 1000 cell filled with info. Some cells contain 3 digits other contain 4 digits. What I want for the 3-digits cell is that a 0 is added before the digits.

Like this:

9878 9876 876 123

Becomes:

9878 9876 0876 0123


I uses the custom format function with "0000" as custom, this works, but only if you define this on forehand, not afterwards. Defining it on forhand unfortunately does not work because the cell get their content from a userform which seems to distort the cell format somehow.

Can anyone give me a clue?

thanks a lot.

Dinictus.

[Audiojoe]

No worries man, just highlight the area you want this to happen on, then right click and go to Format Cells. Then go to the Number tab, and choose Custom from the list. In the Type field, just type 0000

Presto

Audiojoe

[Aladin Akyurek]

On 2002-04-10 02:40, Dinictus wrote:
Hi all,

I have about a 1000 cell filled with info. Some cells contain 3 digits other contain 4 digits. What I want for the 3-digits cell is that a 0 is added before the digits.

Like this:

9878 9876 876 123

Becomes:

9878 9876 0876 0123


I uses the custom format function with "0000" as custom, this works, but only if you define this on forehand, not afterwards. Defining it on forhand unfortunately does not work because the cell get their content from a userform which seems to distort the cell format somehow.

Can anyone give me a clue?

thanks a lot.

Dinictus.

=IF(LEN(A1)=3,"0"&A1,""&A1)

will add a leading zero if A1 houses a 3-digit entry. The result is a 4-digit text entry.

Aladin

[Dinictus]

Thanks guys,

Indeed this works. But I have now pin-pointed the problem: it works with cell I fill manually, but if the cell are filled by my userform app. (audiojoe, you know I got a concept from you earlier on?) I cannot change the format afterwards. Anyone who can explain that?

Darn it I`ve been working on this for too long now.

[Tom Schreiner]

Here, try it like this:

Where text1.text = 0987

Range("A1").value = " ' " & text1.text

= Quotes,apostrophe,quotes & text1.text
with no spaces. Spaces for visibility here.

Tom

[ This Message was edited by: TsTom on 2002-04-10 03:11 ]

[Aladin Akyurek]

On 2002-04-10 02:45, Audiojoe wrote:
No worries man, just highlight the area you want this to happen on, then right click and go to Format Cells. Then go to the Number tab, and choose Custom from the list. In the Type field, just type 0000

I'd be worried when the entry is 123 and it looks by your formatting like it's 0123, while the real 0123 is needed in a VLOOKUP formula to retrieve, say, the price associated with it.

[Mark W.]

=RIGHT(0&A1,4)