Problem with the VALUE function

[Guest]

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

 

[Niek Otten]

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

 

[Roger Govier]

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

 

[Guest]

Hi Roger.

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

Thanks,

-Michael

 

[Dave Peterson]

Maybe you can force it to not look like a date:

=VALUE("000"&"02-5302")
or
=VALUE("000"&A1)

 

[Roger Govier]

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

 

[Guest]

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

 

[Guest]

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]

Hi Michael

I think Dave gave you the answer then, by just adding the "000" in front of
your data.


Regards

Roger Govier

 

[Guest]

Great! It worked!... Thank you so much!

-Michael