Comparing values with leading zeros (1 Viewer)

[tmyers]

I have been searching for an hour to figure this out to no avail.

I am trying to delete rows if the user entered condition matches a cell value but the value to compare it to has leading zeros and therefore doesn't match.

Example:
User enters the value "2", loop through the column on another sheet and if the value is like "2", delete the row. This does not work if the value in the center to check is 002 00 but if they enter 123 and the value is 123 00 it will delete the row. I am even trying using a wildcard but it still wont delete 002 if the entered value is 2.

For Each num In RowsToDrop
    For i = lastrowC To 7 Step -1
        If Report.Range("A" & i).Value Like num & "*" Then
            Report.Rows(i).Delete
        End If
    Next i
Next num

I have tried wrapping

Report.Range("A" & i).Value

in Val(), but then it will delete everything containing a 2. For the record,

num

is shown as a variant/string and the value in the range being compared is a string. The value in the range is alphanumeric, so converting to integer/long would not work.

If I manually change the value from 002 00 to 2 00, it gets deleted correctly. How can I correctly match the two values without modifying the data as it has to keep the format it is in.

 

[The_Doc_Man]

Do the conversion to a number format, e.g. to long or double or currency format, where a leading zero doesn't exist. THEN do the comparison. If the number-string is so big that even Currency format won't hold it, or if more than four decimal places are involved in a fraction, you've got a bigger problem that would require text parsing to fix.

 

[tmyers]

The number string never goes beyond 000 00 but once you get to 999 00, it becomes alphanumeric and would be AAA 00, AAB 00 etc. Wouldn't that cause an error if you tried to convert it to a number?

 

[The_Doc_Man]

Oh, that would have been good to know up front. The problem comes back to this: If your string is a mixed-format value that COULD be mostly numeric but COULD include text characters, you ALWAYS have to compare as a string. So back to your original question.

A comparison inherently involves TWO things, and you are making it clear that one of the two things is more or less fixed in format. In order to make the comparison work, you have to convert the OTHER thing (other comparand) to the format that is fixed in nature. I see you have an embedded space, too. String comparisons are easy in one sense, but the problem you have isn't the comparison. Its the preparation for comparison.

You are in Excel, so there is the added problem that values based on numbers are ambiguous. A numeric string can be interpreted as a number AND as a string because of the nature of the CELL, which is not an object but a property (according to the documentation.) A RANGE object can be narrowed down to a single cell and in that case has a uniquely specified value. Whatever you use, you have to recognize that to make a match for a string, you have to match formats with the thing that has the more restrictive format. Which immediately suggests using a FORMAT statement that can more easily produce a string including a space and leading zeros.

 

[tmyers]

Ah that did it.
Added

num = Format(num, "000")

and that lets it work with the "LIKE" test.

 

[Gasman]

Ah that did it.
Added

num = Format(num, "000")

and that lets it work with the "LIKE" test.

Because now it is is string?

 

[The_Doc_Man]

I would say so that BOTH SIDES are now a string. My advice was based on the ambiguity of an all-digit string in Excel, particularly when "General" format is in use. Forcing the (potentially numeric) comparand through a FORMAT function also forces it unambiguously to be a string.

 

[tmyers]

Doc, I do just want to say I always really enjoy your responses. I am not sure what it is, but I always find your responses very insightful and extremely helpful.

 

[The_Doc_Man]

Thanks.

 

[arnelgp]

can you post an excel file with just 1 column (the lookup column).