Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Inserting digits to a column of numbers?

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a column of cells, about 6000 in all, that contain text based numbers, no formulas. The length of the number should contain six digits. The problem is that about half of these cells contain less than six digits.

    I need to add zero's to the number to act as place holder's in those cells that do not contain all six digits.

    Is there a way to add the zero to these cells without performing it manually?

    For example:

    B2750 has the number '98176', but I need it to be '098176'.

    B4821 has the number '4498', but I need it to be '004498'.

    And so on until I have single digit numbers.

    Can anyone help me with this?

    Thank you,
    Jason

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Format-Cells-Custom

    000000
    ~Anne Troy

  3. #3
    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

    I understand that you have text representation of numbers (e.g., "98176") that you want to be fixed-length (6 chars).
    For values in column A enter the formula...

    =REPT(0,6-LEN(A1))&A1

    ...and Fill down as needed. Use Copy/Paste Special Values to preserve the resultant values.

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to both of you for your responses.

    I tried the first suggestion and it seemed to have worked. I'm glad to know it was something so simple. You guys are going to turn me into a pro, yet!

    Thanks again,
    Jason

Some videos you may like

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
  •