MID function from right to left
 

The MID function works from left to right so picking the first three
characters of a string is easy enough, but I need to pick the LAST
three characters of a series of strings with variable length.

Is it possible to do this?

NSV


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=566959

Answer: MID function from right to left
 

Yes, it is possible to use the MID function to pick the last three characters of a string from right to left. Here's how you can do it:

1. Determine the length of the string using the
   Formula:

   ---

   LEN 


   ---

   function. For example, if the string is in cell A1, the formula would be:
   Formula:

   ---

   =LEN(A1) 


   ---

2. Subtract 2 from the length of the string to get the position of the third character from the end. For example, if the length of the string is 10, the position of the third character from the end would be 8.
3. Use the
   Formula:

   ---

   MID 


   ---

   function to extract the last three characters of the string. The formula would be:
   Formula:

   ---

   =MID(A1,LEN(A1)-2,3) 


   ---

This formula tells Excel to start at the position of the third character from the end (

Formula:

---

LEN(A1)-2 


---

) and extract three characters (

Formula:

---

3 


---

) from that position.

So, for example, if the string in cell A1 is "Hello World", the formula would return "rld".

MID function from right to left
 

Try using RIGHT instead of MID


"nsv" wrote in message
...

The MID function works from left to right so picking the first three
characters of a string is easy enough, but I need to pick the LAST
three characters of a series of strings with variable length.

Is it possible to do this?

NSV


--
nsv
------------------------------------------------------------------------
nsv's Profile:
http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=566959

MID function from right to left
 

For example in B1 write :-

=RIGHT(A1,3)

i hope that helps.

GARY

"Gary" wrote in message
...
Try using RIGHT instead of MID


"nsv" wrote in message
...

The MID function works from left to right so picking the first three
characters of a string is easy enough, but I need to pick the LAST
three characters of a series of strings with variable length.

Is it possible to do this?

NSV


--
nsv
------------------------------------------------------------------------
nsv's Profile:
http://www.excelforum.com/member.php...o&userid=26500
View this thread:
http://www.excelforum.com/showthread...hreadid=566959

MID function from right to left
 

But if for some reason you insist on using MID:
=MID(A1,LEN(A1)-2,3)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Gary" wrote in message ...
| For example in B1 write :-
|
| =RIGHT(A1,3)
|
| i hope that helps.
|
| GARY
|
| "Gary" wrote in message
| ...
| Try using RIGHT instead of MID
|
|
| "nsv" wrote in message
| ...
|
| The MID function works from left to right so picking the first three
| characters of a string is easy enough, but I need to pick the LAST
| three characters of a series of strings with variable length.
|
| Is it possible to do this?
|
| NSV
|
|
| --
| nsv
| ------------------------------------------------------------------------
| nsv's Profile:
| http://www.excelforum.com/member.php...o&userid=26500
| View this thread:
| http://www.excelforum.com/showthread...hreadid=566959
|
|
|
|
|