RIGHT, LEFT & MID function

Question

I don't understand why the RIGHT, LEFT & MID functions are not producing the results I am expecting.

The information I need is displayed in the 'fx' dialogue box, however, when I come out of the cell with the function all that is displayed is '=RIGHT(E2,9).

Thoughts?

sergeibaklan · Answer

Peggy Lang&nbsp;
Please check if you are in Automatic mode

peggy lang · Answer

SergeiBaklan&nbsp;&nbsp;&nbsp;I am in 'Automatic' mode; although I'm not sure what that means.&nbsp; Still doesn't work?&nbsp; Not sure what's going on&nbsp;

mathetes · Answer

It also occurs to me that you might have the cell formatted as "Text" which could preclude it operating as a function.

sergeibaklan · Answer

Peggy Lang&nbsp;
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.
&nbsp;
If that doesn't work better if you provide sample file to check.

mathetes · Answer

Peggy Lang&nbsp;&nbsp;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&nbsp; 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.&nbsp;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.

Forum Discussion

RIGHT, LEFT & MID function

8 Replies

Resources