How to select text in Excel 2010 like MID function but from end of text rather than beginning?
Instead of MID which starts at the beginnng of a line of text, the formula or user-defined function that would start at the end of the text and search toward the beginning. I need to select from the end of the long text up to dash for a result of: View > Windows > Arrange All
Arrange All - Tile all open program windows side-by-side on the screen. - View > Window > Arrange All
Arrange All - Tile all open program windows side-by-side on the screen. - View > Window > Arrange All
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you prefer a VBA user-defined function, consider the following code. Install it in a regular module sheet (just like a macro), then use a worksheet formula like:
=LastDashToEnd(A1)
As written, the user-defined function removes leading and trailing spaces with the VBA Trim function. The VBA Trim function does not remove space characters that are repeated in the interior of the returned string.
In case you might want to repurpose the user-defined function for a character (or string) other than a hyphen, I included an optional parameter in the function where you can specify such.
=LastDashToEnd(A1)
As written, the user-defined function removes leading and trailing spaces with the VBA Trim function. The VBA Trim function does not remove space characters that are repeated in the interior of the returned string.
In case you might want to repurpose the user-defined function for a character (or string) other than a hyphen, I included an optional parameter in the function where you can specify such.
Function LastDashToEnd(SomeText As String, Optional FindText As String = "-") As String
'Returns text from SomeText starting after the last instance of FindText. Returned text continues to end of SomeText.
'Use with worksheet formula like: _
=LastDashToEnd(A1)
'If you want to specify a different character (or string) to find in SomeText, use the FindText parameter like this: _
=LastDashToEnd(SomeText, "#") returns text starting with last # in SomeText
Dim n As Long
n = InStrRev(FindText & SomeText, FindText)
LastDashToEnd = Trim(Mid(FindText & SomeText, n + Len(FindText)))
End Function
ASKER