+ Reply to Thread
Results 1 to 5 of 5

Insert Digits Into A Column of Numbers

Hybrid View

  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:
    ="0"&LEFT(A1,3)&"0"&MID(A1,4,2)&"0"&RIGHT(A1,2)
    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