+ Reply to Thread
Results 1 to 5 of 5

Insert Digits Into A Column of Numbers

  1. #1
    Registered User
    Join Date
    02-15-2008
    Posts
    5

    Insert Digits Into A Column of Numbers

    I have a column with hundreds of 7-digit numbers. I want to convert all of the numbers to 10-digit numbers with zeros inserted into the number, filling the first, fifth, and eigth digit. For instance, I have a 7-digit number xxxxxxx and I want to change it to yxxxyxxyxx where the y's are zeros. The solution I'm looking for will convert the entire column into 10-digit numbers in this manner.

    I hope whoever reads this can understand it, as it is somewhat difficult to explain. I'm not sure if Excel is capable of inserting digits into numbers like this, but I sure hope so, as changing them all individually would be extremely tedious.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You can format the cells as "0"000"0"00"0"00 in place, or use an adjacent column:

    =TEXT(A1,"""0""000""0""00""0""00")

  3. #3
    Registered User
    Join Date
    02-15-2008
    Posts
    5
    Thank you for your help. Doing what you said, I managed to get the cells in the worksheet to display the numbers the way I want, but in the formula bar it still displays the number as the original 7-digit number. It seems that it treats it as the 7-digit number too, for instance if I use the find tool and try to find the 10-digit number (with the zeros added) it won't find it, but if i search for the 7-digit number then it will find it even though it displays the number as 10-digits in the cells. Is there a way to make it so that Excel not only displays it with the zeros added but treats it as the new 10-digit number?

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Hi grayfox,

    In a column next to (or near, doesn't matter) your data, use the formula:
    Please Login or Register  to view this content.
    This assumes your data starts in A1. Fill that formula down as many rows as needed. If you want, you can then copy the new column and then Edit -> PasteSpecial -> Values to leave just the 10-digit codes and not the formulas.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Paul's approach will give you the same result as my second suggestion, and both give a text result. If you want a numeric result, use

    =VALUE(TEXT(A1, "000""0""00""0""00"))

    ... and format as 0000000000 to show the leading zero.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1