Forum Discussion
RIGHT, LEFT & MID function
I am in 'Automatic' mode; although I'm not sure what that means. Still doesn't work? Not sure what's going on
One more if Show Formula is checked
Or another dozen of reasons. Discussion is too abstract.
- I went internal to a colleague whom I thought may have worked with Excel and different functions. He told me to press 'ctrl + ~'. VOILA!!! It worked. Obviously it's a keyboard shortcut that toggles between the action of the actual function and the syntax of the function.
Thank YOU all who responded. Love this community and the collaborative nature of everyone.
Let me also suggest that by only pulling off the 9 right-most characters you're going to not get the results you want with dates that have 10 or 8 characters. So here's an alternative way to write the formula, assuming your same dates (in text) in cell A1
=RIGHT(A1,LEN(A1)-FIND("-",A1)-1)
How this operates is that it finds the position of the little hyphen that separates the two dates,.subtracts that from the length of the full text, and then subtracts 1 more...to get the actual length of the date to the right of the hyphen, however long it happens to be.
And, for what it's worth, you can also embed that in a DATEVALUE function so as to render the date in a manner that would enable Excel to perform date math.
=DATEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)-1))
See the attached for working example.
If you see the formula instead of result, another possible reason is the cell in which you enter the formula is formatted as Text. You may check by Ctrl+1 on such cell. If so change format on General and re-enter the formula.
If that doesn't work better if you provide sample file to check.
