RIGHT, LEFT & MID function

%3CLINGO-SUB%20id%3D%22lingo-sub-2384478%22%20slang%3D%22en-US%22%3ERIGHT%2C%20LEFT%20%26amp%3B%20MID%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384478%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20understand%20why%20the%20RIGHT%2C%20LEFT%20%26amp%3B%20MID%20functions%20are%20not%20producing%20the%20results%20I%20am%20expecting.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EThe%20information%20I%20need%20is%20displayed%20in%20the%20'fx'%20dialogue%20box%2C%20however%2C%20when%20I%20come%20out%20of%20the%20cell%20with%20the%20function%20all%20that%20is%20displayed%20is%20'%3DRIGHT(E2%2C9).%3CBR%20%2F%3E%3CBR%20%2F%3EThoughts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2384478%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384914%22%20slang%3D%22en-US%22%3ERe%3A%20RIGHT%2C%20LEFT%20%26amp%3B%20MID%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F101312%22%20target%3D%22_blank%22%3E%40Peggy%20Lang%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20if%20you%20are%20in%20Automatic%20mode%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20499px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283637i78A5878FF4F7CB0F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384921%22%20slang%3D%22en-US%22%3ERe%3A%20RIGHT%2C%20LEFT%20%26amp%3B%20MID%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EI%20am%20in%20'Automatic'%20mode%3B%20although%20I'm%20not%20sure%20what%20that%20means.%26nbsp%3B%20Still%20doesn't%20work%3F%26nbsp%3B%20Not%20sure%20what's%20going%20on%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384930%22%20slang%3D%22en-US%22%3ERe%3A%20RIGHT%2C%20LEFT%20%26amp%3B%20MID%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384930%22%20slang%3D%22en-US%22%3EIt%20also%20occurs%20to%20me%20that%20you%20might%20have%20the%20cell%20formatted%20as%20%22Text%22%20which%20could%20preclude%20it%20operating%20as%20a%20function.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384938%22%20slang%3D%22en-US%22%3ERe%3A%20RIGHT%2C%20LEFT%20%26amp%3B%20MID%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384938%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F101312%22%20target%3D%22_blank%22%3E%40Peggy%20Lang%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20see%20the%20formula%20instead%20of%20result%2C%20another%20possible%20reason%20is%20the%20cell%20in%20which%20you%20enter%20the%20formula%20is%20formatted%20as%20Text.%20You%20may%20check%20by%20Ctrl%2B1%20on%20such%20cell.%20If%20so%20change%20format%20on%20General%20and%20re-enter%20the%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that%20doesn't%20work%20better%20if%20you%20provide%20sample%20file%20to%20check.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384963%22%20slang%3D%22en-US%22%3ERe%3A%20RIGHT%2C%20LEFT%20%26amp%3B%20MID%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F101312%22%20target%3D%22_blank%22%3E%40Peggy%20Lang%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20also%20suggest%20that%20by%20only%20pulling%20off%20the%209%20right-most%20characters%20you're%20going%20to%20not%20get%20the%20results%20you%20want%20with%20dates%20that%20have%26nbsp%3B%2010%20or%208%20characters.%20So%20here's%20an%20alternative%20way%20to%20write%20the%20formula%2C%20assuming%20your%20same%20dates%20(in%20text)%20in%20cell%20A1%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DRIGHT(A1%2CLEN(A1)-FIND(%22-%22%2CA1)-1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHow%20this%20operates%20is%20that%20it%20finds%20the%20position%20of%20the%20little%20hyphen%20that%20separates%20the%20two%20dates%2C.subtracts%20that%20from%20the%20length%20of%20the%20full%20text%2C%20and%20then%20subtracts%201%20more...to%20get%20the%20actual%20length%20of%20the%20date%20to%20the%20right%20of%20the%20hyphen%2C%20however%20long%20it%20happens%20to%20be.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1621966439614.png%22%20style%3D%22width%3A%20507px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283649i22F821DB8EF6CAA8%2Fimage-dimensions%2F507x199%3Fv%3Dv2%22%20width%3D%22507%22%20height%3D%22199%22%20role%3D%22button%22%20title%3D%22mathetes_0-1621966439614.png%22%20alt%3D%22mathetes_0-1621966439614.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%2C%20for%20what%20it's%20worth%2C%20you%20can%20also%20embed%20that%20in%20a%20DATEVALUE%20function%20so%20as%20to%20render%20the%20date%20in%20a%20manner%20that%20would%20enable%20Excel%20to%20perform%20date%20math.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DDATEVALUE(RIGHT(A1%2CLEN(A1)-FIND(%22-%22%2CA1)-1))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20the%20attached%20for%20working%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Peggy Lang 

Please check if you are in Automatic mode

image.png

@Sergei Baklan 

 

 

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

 

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

@Peggy Lang 

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.

@Peggy Lang 

 

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.

mathetes_0-1621966439614.png

 

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.

@Peggy Lang 

One more if Show Formula is checked

image.png

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.

@Peggy Lang 

That is shortcut for Show Formulas on ribbon