Discussion Re: RIGHT, LEFT & MID function in Excel
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384963#M101177
<P><LI-USER uid="101312"></LI-USER> </P><P> </P><P>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</P><P><STRONG>=RIGHT(A1,LEN(A1)-FIND("-",A1)-1)</STRONG></P><P>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.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="mathetes_0-1621966439614.png" style="width: 507px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/283649i22F821DB8EF6CAA8/image-dimensions/507x199?v=v2" width="507" height="199" role="button" title="mathetes_0-1621966439614.png" alt="mathetes_0-1621966439614.png" /></span></P><P> </P><P>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.</P><P><STRONG>=DATEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)-1))</STRONG></P><P>See the attached for working example.</P>Tue, 25 May 2021 18:15:50 GMTmathetes2021-05-25T18:15:50ZRIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384478#M101154
<P>I don't understand why the RIGHT, LEFT & MID functions are not producing the results I am expecting.<BR /><BR /></P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P>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).<BR /><BR />Thoughts?</P><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P> </P>Tue, 25 May 2021 16:17:14 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384478#M101154Peggy Lang2021-05-25T16:17:14ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384914#M101169
<P><LI-USER uid="101312"></LI-USER> </P>
<P>Please check if you are in Automatic mode</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 499px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/283637i78A5878FF4F7CB0F/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>Tue, 25 May 2021 17:45:05 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384914#M101169Sergei Baklan2021-05-25T17:45:05ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384921#M101170
<P><LI-USER uid="521"></LI-USER> </P><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P>I am in 'Automatic' mode; although I'm not sure what that means. Still doesn't work? Not sure what's going on</P><P> </P>Tue, 25 May 2021 17:49:14 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384921#M101170Peggy Lang2021-05-25T17:49:14ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384930#M101173
It also occurs to me that you might have the cell formatted as "Text" which could preclude it operating as a function.Tue, 25 May 2021 17:56:15 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384930#M101173mathetes2021-05-25T17:56:15ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384938#M101174
<P><LI-USER uid="101312"></LI-USER> </P>
<P>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.</P>
<P> </P>
<P>If that doesn't work better if you provide sample file to check.</P>Tue, 25 May 2021 17:56:57 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384938#M101174Sergei Baklan2021-05-25T17:56:57ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384963#M101177
<P><LI-USER uid="101312"></LI-USER> </P><P> </P><P>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</P><P><STRONG>=RIGHT(A1,LEN(A1)-FIND("-",A1)-1)</STRONG></P><P>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.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="mathetes_0-1621966439614.png" style="width: 507px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/283649i22F821DB8EF6CAA8/image-dimensions/507x199?v=v2" width="507" height="199" role="button" title="mathetes_0-1621966439614.png" alt="mathetes_0-1621966439614.png" /></span></P><P> </P><P>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.</P><P><STRONG>=DATEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)-1))</STRONG></P><P>See the attached for working example.</P>Tue, 25 May 2021 18:15:50 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2384963#M101177mathetes2021-05-25T18:15:50ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2385371#M101208
<P><LI-USER uid="101312"></LI-USER> </P>
<P>One more if Show Formula is checked</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 592px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/283686i30E5D37B6E10226E/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>Or another dozen of reasons. Discussion is too abstract.</P>Tue, 25 May 2021 20:43:08 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2385371#M101208Sergei Baklan2021-05-25T20:43:08ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2385649#M101227
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.<BR /><BR />Thank YOU all who responded. Love this community and the collaborative nature of everyone.Tue, 25 May 2021 22:41:08 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2385649#M101227Peggy Lang2021-05-25T22:41:08ZRe: RIGHT, LEFT & MID function
https://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2388895#M101385
<P><LI-USER uid="101312"></LI-USER> </P>
<P>That is shortcut for Show Formulas on ribbon</P>Wed, 26 May 2021 20:27:39 GMThttps://techcommunity.microsoft.com/t5/excel/right-left-amp-mid-function/m-p/2388895#M101385Sergei Baklan2021-05-26T20:27:39Z