Hi Roger.

I use that later on when I am trying to find certain issues, but I am using
the value function to initially identify the data that is a problem.

I want to be generic because there can be a plethora of problems like
slashes, alpha chars, and/or special chars. The dash is only one instance of
the problem.

The other issue I have is that I cannot change the data. I can only look at
it and mark whether it has a certain issue.

Sorry, I know this is difficult and confusing (Trust me!), but I really
appreciate your help.

Thanks,

-Michael

"Roger Govier" wrote:

Hi Michael

I misunderstood what you were trying to do, although my explanation of the
result you are getting is correct.

If you are just checking to make sure there are no "-" entries within a
cell, then you could use

=IF(NOT(ISERROR(FIND("-",A7)0)),"error","ok")

Regards

Roger Govier


Michael wrote:
Hi Roger.

I tried inputting the data as =" '02-5302' ", but it reads it as literally
'02-5302'... I am missing something?

Thanks,

-Michael

"Roger Govier" wrote:


Hi Michael

The problem is Excel trying to be too helpful!!!
It is seeing 02-5302 as you wanting to enter Feb 5302 (rather a long time
ahead).

It doesn't try to do that with 00, as there cannot be a month 0, but it will
do it with values through 01 to 12, which is why your 020-5302 works.

One way round it would be to warp the value inside single quotes within your
double quotes

=VALUE(" '020-5302' ") (deliberately spaced out to show the single quote)

Regards

Roger Govier


Michael wrote:

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