Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Delete 1st byte of text if it meets criteria (Excel 97)

    I have a field that reports case numbers in the following format: 01234-01AUG03 or 3272801AUG03. I've already created a formula to delete the dash from the 1st case, leaving 0123401AUG03. Now to do an exact comparison to another list of case numbers, I need to delete the leading zero in those cases, leaving 123401AUG03. Is there a formula that can be written to identify if there is a zero as the 1st byte of the field and if so, delete the 1st byte only?
    thanks
    christine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete 1st byte of text if it meets criteria (Excel 97)

    Say that your data is in cell A2 and below. If necessary, insert an empty column next to it, and enter the following formula in cell B2:

    =IF(LEFT(A2,1)="0",MID(A2,2,999),A2)

    Fill down as far as necessary, and use column B in the comparison.

    Notes:
    The 999 is an arbitrary number larger than the longest length of an entry in column A.
    You can hide column B if you don't want to display it.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Delete 1st byte of text if it meets criteria (Excel 97)

    For a complete solution:
    =SUBSTITUTE(SUBSTITUTE(LEFT(A1,1),0,"")&RIGHT(A1,L EN(A1)-1),"-","")

    Cheers
    Cheers,

    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
  •