I do not think there is a Function to do that.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.
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
Row\Col | A | B |
---|---|---|
16 | 123456789 | =Midr(A16,7,4) |
Row\Col | A | B |
---|---|---|
16 | 123456789 | 3456 |
' 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
Row\Col | A | B |
---|---|---|
20 | Hello There My Friend | =MidFromRight(A20,9,2) |
Row\Col | A | B |
---|---|---|
20 | Hello There My Friend | My |
Or even...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)
'
' 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
Row\Col | A | B |
21 | Hello There My Friend | =LeftRight(A21,9,2) |
22 | =LEFT(RIGHT(A21,9),2) |
Row\Col | A | B |
21 | Hello There My Friend | My |
22 | My |