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

1. ## 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?

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

#### Posting Permissions

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