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

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

For a complete solution:
=SUBSTITUTE(SUBSTITUTE(LEFT(A1,1),0,””)&RIGHT(A1,LEN(A1)-1),”-“,””)

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote