A few cells with 3 digits, I want 4 digits: 345 -> 0345
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

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

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.




  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    =RIGHT(0&A1,4)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com