# RIGHT, LEFT & MID function

Occasional Contributor

# RIGHT, LEFT & MID function

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?

8 Replies

# Re: RIGHT, LEFT & MID function

Please check if you are in Automatic mode

# Re: 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

# Re: RIGHT, LEFT & MID function

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

# Re: RIGHT, LEFT & MID function

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.

# Re: RIGHT, LEFT & MID function

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.

# Re: RIGHT, LEFT & MID function

One more if Show Formula is checked

Or another dozen of reasons. Discussion is too abstract.

# Re: RIGHT, LEFT & MID function

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.

# Re: RIGHT, LEFT & MID function

That is shortcut for Show Formulas on ribbon