Command like MID that takes characters from the end of a string instead of the front?

dan7055

Active Member
Joined
Jul 9, 2015
Messages
312
I'm looking for a command like MID that takes a certain amount of characters counting backwards from the end of a string instead of the MID command that takes a certain number of characters starting by counting from the start of the string.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Example: =RIGHT(A2,3) ... this will return the first 3 characters starting from the right in cell A2.
 
Upvote 0
Function like MID that takes characters from the end of a string instead of the front?

Hi dan, :)
I'm looking for a command like MID that takes a certain amount of characters counting backwards from the end of a string instead of the MID command that takes a certain number of characters starting by counting from the start of the string.
I do not think there is a Function to do that.

So make one.
( macro stuff!! )

Like this:.....

Rich (BB code):
Sub MidRight()  '   http://www.mrexcel.com/forum/excel-questions/936650-command-like-mid-takes-characters-end-string-instead-front.html#post4499546
Dim str As String
Let str = "123456789"
Dim str3456 As String
Let str3456 = Mid(str, 3, 4) ' takes a certain number of characters starting by counting from the start ( Left )  of the string
Let str3456 = Midr(str, 7, 4) ' takes a certain amount of characters counting backwards from the end ( right ) of a string
End Sub
Public Function Midr(ByVal str As String, ByVal countingbackwardsfromtheend As Long, ByVal certainamountofcharacters As Long) As String
Let Midr = Mid(str, ((Len(str) - countingbackwardsfromtheend) + 1), certainamountofcharacters)
End Function

If you are new to macro stuff.... Do that Function making like this:

IMPORTANT: always save your program just before testing or running any program. –This is Because you cannot use the Excel backward thing to clear any changes made by a macro.
(Note: When you ““work” on your File” you effectively work on a “copy” of your File. It only becomes your File when you save - So If anything goes wrong after a macro runs, simply close the “copy” File without saving, then open your File, and no damage will have been done to your original File).



To Install a macro:

1. Copy the codes given to the clipboard. ( Highlight code and Hit Ctrl C ) ( Actually for you only the Function is important, but copy all that stuff above - it does no harm )
2. Open a NEW workbook ( or the one you want the code ( Function to work ) in )
3. Press the keys ALT + F11 to open the Visual Basic Editor
At this point you should see the cursor flashing in a big empty window
( If not then select from the VB Editor ribbon ---- Insert ------ module )
4. Where the cursor is flashing, paste the code
( 5. Do any editing of the code you wish to at this stage. )
6. Press the keys ALT + F11 to return to Excel ( or press ALT and q simultaneously to close the VB Editor )
7. When in Excel, press ALT + F8 to display the Run Macro Dialog.
8. You can choose to run the code Sub MidRight() ( alternatively Double Click the macro's name to Run it.)
Or
9. You can choose to work on it. – This option is good to find the macro if you have many and are not sure where they all are )

_.................................

Also, Now you can use that new self made Function ( UDF .. User Defined Function ) in any Sheet in the File in which the Public Function is in

Just try this: Type in....

Using Excel 2007
Row\Col
A
B
16
123456789​
=Midr(A16,7,4)

_...

You should get:...


Using Excel 2007
Row\Col
A
B
16
123456789​
3456


<marquee direction="down" width="30" height="40" behavior="alternate" >
<marquee behavior="alternate">
<strike>:)</strike>
</marquee>
</marquee> )

Alan Elston
 
Last edited:
Upvote 0
You could combine existing functions to do what you're trying.

A1 = "Hello There My Friend"
You wanted to count backwards 9 characters from the right, and return 2 characters to get "My"

In my imagination it would look like
=MidFromRight(A1,9,2)

You can combine MID and LEN to get this done.
=MID(A1,LEN(A1)-9+1,2)
 
Upvote 0
Jonmo1 version .....

Rich (BB code):
'  Jonmo1       http://www.mrexcel.com/forum/excel-...ers-end-string-instead-front.html#post4499565
Public Function MidFromRight(ByVal str As String, ByVal countingbackwardsfromtheend As Long, ByVal certainamountofcharacters As Long) As String
Let MidFromRight = Mid(str, ((Len(str) - countingbackwardsfromtheend) + 1), certainamountofcharacters)
End Function



Using Excel 2007
Row\Col
A
B
20
Hello There My Friend=MidFromRight(A20,9,2)



Using Excel 2007
Row\Col
A
B
20
Hello There My FriendMy
 
Last edited:
Upvote 0
You could combine existing functions to do what you're trying.

A1 = "Hello There My Friend"
You wanted to count backwards 9 characters from the right, and return 2 characters to get "My"

In my imagination it would look like
=MidFromRight(A1,9,2)

You can combine MID and LEN to get this done.
=MID(A1,LEN(A1)-9+1,2)
Or even...

=LEFT(RIGHT(A1,9),2)
 
Upvote 0
Rick version

Rich (BB code):
'
'   Rick   http://www.mrexcel.com/forum/excel-questions/936650-command-like-mid-takes-characters-end-string-instead-front.html#post4499624
Public Function LeftRight(ByVal str As String, ByVal countingbackwardsfromtheend As Long, ByVal certainamountofcharacters As Long) As String
Let LeftRight = Left(Right(str, countingbackwardsfromtheend), certainamountofcharacters)
End Function


Using Excel 2007
Row\Col
A​
B​
21​
Hello There My Friend=LeftRight(A21,9,2)
22​
=LEFT(RIGHT(A21,9),2)



Using Excel 2007
Row\Col
A​
B​
21​
Hello There My FriendMy
22​
My
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top