| Home |
| Search |
| Today's Posts |
|
#7
|
|||
|
|||
Great! It worked!... Thank you so much!
-Michael "Dave Peterson" wrote: Maybe you can force it to not look like a date: =VALUE("000"&"02-5302") or =VALUE("000"&A1) 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 -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count function problem | Excel Worksheet Functions | |||
| Lookup function problem (kg) | Excel Worksheet Functions | |||
| Problem with function "Worksheet_Change" | Excel Worksheet Functions | |||
| Problem adding a range using Sumif function. | Excel Worksheet Functions | |||
| Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Threaded Mode
