I am trying to use the IF feature. Basically, if cell G2 does not have anything in it, the cell is true and it returns just a "" or a blank. If it has a date in it, then it is false, and I want it to input the YEAR of that cell. I figure this has something to do with excel not understand that the cell to test the logic is a date in the first place, but I don't know what to use here. Cell G2 format is already set to date. if true = "" (or just left blank) if false = 2016 =IF(G2="","","=YEAR(G2)") is my formula, and it is currently just returning verbatim the =YEAR(G2), when in reality, I want it to put in the year of cell G2.
Try to remove double quotation marks from around 'YEAR': =IF(G2="","",=YEAR(G2)) And may be use YEAR without '=': =IF(G2="","",YEAR(G2)) - I mean when you put '=YEAR(G2)' you state that the cell should hold the formula. When you put 'YEAR(G2)' you state that the cell should hold the value equals to the year of the date. https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2 https://exceljet.net/excel-functions/excel-if-function
This one works. Thanks The quotes just instruct the formula to spell out verbatim what is inside.....however, my original formula does not work without the quotes, something to do with using G2="" vs the ISBLANK command.
Ok, now I have a new problem with excel. I usually have large stacks of numbers (usually between 20-40 rows), and they are all 5 or 6 digit figures. The sum feature does not work on my spreadsheet. There is a leading space, and a trailing space on each number. I can say =TRIM(A1) to dump the spaces, which does what I want, but you cannot add a column of TRIM commands....I tried pasting the numbers using the special values paste into the next column over, but then it copies the junk spaces again. How do I TRIM the leading and trailing spaces, then sum up my columns?
With a leading and/or trailing space, Excel thinks it is text. Convert the string to a number. The formula uses NUMBERVALUE. EDIT: better of using "=VALUE (TRIM(cell with text))"
Hmmmm. How do I convert to NUMBERVALUE? If I go to the ribbon in the number section and click the drop down....if I change from "general" to "number" it doesn't seem to do anything, but it adds a decimal place to my SUM formula (still at 0). If I do =VALUE(TRIM(A1)) it returns an error. All the numbers are in column A, starting at A1....I would just drop the real numbers into B1 I guess.
Was trying to account for spaces if they existed. =value(cell reference) should do the trick ie =value(A1)
B2 formula is just =TRIM(A1)....but it excel doesn't see it as a number, so I can't copy those figures and paste them or use the SUM feature. I don't get it.
Select the data area and you should see the excl. mark appear. Click on it and select Convert to number. The dropdown box in ribbon chooses cell formatting but doesn't convert the data. So formatting a cell with number as text to number will still have text type data.
Strange how the TRIM function works but not the VALUE function. Never have run across that before. Always was that if one worked the other one would. AsiJu's suggestion should work. @AsiJu that is a quirk that bugs the living daylights out of me. The only solution has been to F2 and "enter" through the cells. Then the formatting in the dropdown box takes effect.
It's a little box that looks like it has an exclamation mark "!" in it. It should popup at the topmost cell of the ones highlighted.
Yeah I agree, can't understand why it's still an issue with Excel. Can't recall how many hours I've wasted in total troubleshooting formulae like in the topic here only because the data in number cells is text instead... Exactly like the one you have in the first pic posted in B column after 2,000, box with a excl. mark. When you select a cell or group of cells formatted as number with number stored as text, the icon shows up and clicking it gives you an option to Convert to number (the data in the cells). After that, when both the cell range and data are in number form, the formula should work. Or at least stop returning VALUE error
