Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Los Angeles, California, USA
    Thanked 0 Times in 0 Posts

    Text String (Excel 97)

    Scenario#1: I have a column that contain text strings which has 10 digits. For example - 0000012345, 9987793140, and 0088901250. Task: I would like to extract the numbers that are equal to or greater than 1. For example, 0000012345 should end up 12345. But, if it is already 10 digits and no leading zeros the return that string, like 9987793140.
    Scenario#2: I have a column that contains text strings that are less than 10 digits; hence, I would like to add the leading zeros. I have been using the LEN and a nested IF to count and concatenate the number of leading zeros. I use two columns - one for LEN while the other if for the IFs. Is there a shortcut? Thanks - GCB

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Text String (Excel 97)

    1. Say your strings are in column A, starting at A1. In cell B1, enter the formula =VALUE(A1). Fill down as far as necessary.

    2. As above, but format the cells in column B as 0000000000
    Or, if you want strings, =TEXT(VALUE(A1),"0000000000")
    Or =REPT("0",10-LEN(A1))&A1 (explanation: the REPT function repeats a string a specified number of times)
    Or =RIGHT("0000000000"&A1,10)

  3. #3
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 470 Times in 387 Posts

    Re: Text String (Excel 97)

    Hi GCB,

    For scenario 1, you could delete all the leading 0s by selecting any empty cell, copying it, the doing a Paste Special|Add over the affected cells. This converts all the text strings to true numbers, for which Excel doen't normally display leading 0s.

    For scenario 2, apply scenario 1, then force the display of leading 0s via a custom format, eg:


    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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