Allow wildcards to be used with IF statements
I think the IF function should allow wildcards to be used. Take a look at the attached file. I'd rather write the below function than what I have show in the attached filed:
=IF(N2=2010??, "2010", IF(N2=2011??, "2011",...etc.)
Actually, never noticed the example file before.
@Narendra: No need to "--" the LEFT() function as its result from this data is a true number if the data is a true number (and the way it is crafted, much of the point is to allow it to be exactly that), or, while returned as text Excel will still evaluate it as a number in any formula relying upon it.
Also, whether text or number, the way things are formed for the example, the comparisons work as text OR numbers.
Same idea for the YEAR() formula you show. No need for the "--" trick.
That said, one WOULD need to know whether the result would be returned as number or text for setting up the comparison: if number, use IF() to compare to a number (say, 2020), if text, it has to be compared to a string (say, "2020"). So the need for the "--" trick is to simplify the comparison rather than using something like an OR() to test both ways at once, or even testing the kind of value (ISTEXT(), etc. to pick the comparison, to show how ridiculously complicated one could get. The "--" neatly handles all that with great simplicity and works on the returned data whether it is a number or text.
@Alex T.: That comparison being done with all the IF()'s (column M) that need added to every year... you can simplify it amazingly using a VLOOKUP() on a table with the "year break" values in column 1 and the year to return in column 2. Use "TRUE" for the last argument and set up the table accordingly. VLOOKUP then performs all the comparisons so you can drop out all the IF()'s.
There are other functions that somewhat take care of this. Like:
add "--" before above function to convert the result back to numeric value: =--LEFT(N2,4)
another one is: YEAR(--TEXT(N2,"0000-00-1")
Or use string functions if the data is well-formed, or uniquely so for the string of choice.
But we do it SO often... it should be a feature, not a series of workarounds and data conditioning.
How will they ever do computing the way Sci-Fi does, just talking to computers, if everything has to be spelled out, debugged, tested, then bug-fixed every use and all data requests have to imagine every way data could be adulterated?
Alex T. commented
I could just split the value using the "text to columns" feature, but just thought I'd share something that could improve Excel!