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

Dinictus

Board Regular
Joined
Mar 19, 2002
Messages
162
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top