Hi Niek.

Here is what I am trying to do. I have two sets of data that I need to
compare. For example, I want to compare 025302 against 02-5302 (it could be
00000025302 or 025302FDHDH there are only a few cases). The first thing I
check is the values. My assumption is that if it returns an error, then there
is some non-number character invloved and I would precede to mark it
accordingly. In the case of 025302 against 02-5302, I would expect
Value(02-5302) to return #Value! error since there is a dash. Then I would
search for certain chars and find out that it has a dash. So this instance
would be marked as having a "dash issue".

My function works on the great majority of the data involved except for the
instances where it is like 01, 02 ... etc because of the date issue you
mentioned. Is there a work around I could do to avoid this issue? Or is
there a way to override the conversion to the date format?

I really appreciate your help.

Thanks,

-Michael

"Niek Otten" wrote:

Hi Michael,

What you get is the number of days since the year 1, for Feb 1, 5302.

What did you require the formula to return and why?

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Michael" wrote in message
...
Hey guys!

I was wondering if someone can help me. I am trying to parse through two
sets of data in Excel in order to identify certain problems between them.
Problems include additional miscellaneous characters, dashes instead of
zeros, extra zeros in the beginning, etc.

I am writing functions for each case using the VALUE function to
originally
identify a data that is a problem because it will return an error when
there
is something not right with the string. So, I would check VALUE("00-5220")
which would give an error, hence I know that the data has an issue and can
check from there.

However if I do, VALUE("02-5302"), the result is 1242588 (Which ruins the
whole check I am doing because it will skip it). However, if I add a zero,
so
=VALUE("020-5302") the result is an error (Which is what I want).

Can someone explain this to me? I have been playing around with it for a
while now and I cannot figure it out. My observations indicate that if
there
is a "0" before the dash, there will be an error as expected. However, any
other number before the dash will return some number.

I greatly appreciate any assistance you could offer.

Thanks,

-Michael