Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to select text in Excel 2010 like MID function but from end of text rather than beginning?

Posted on 2016-09-25
3
Medium Priority
?
77 Views
Last Modified: 2016-09-25
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
0
Comment
Question by:Alex Campbell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 41815060
Worksheet formula to get text from last hyphen in A1 through end of text in cell (up to 99 characters):
=MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,99)

The formula works by counting the number of hyphens as original length less length with hyphens deleted by SUBSTITUTE. It then uses another SUBSTITUTE function to replace the last hyphen with a pipe | character. It then uses the FIND function to locate the position of the pipe character. Using that location, the MID function then returns the desired text.

I made no attempt to remove the space following the hyphen, though I could have done so by adding a TRIM function to the result.
0
 
LVL 1

Author Closing Comment

by:Alex Campbell
ID: 41815069
Great, thanks
0
 
LVL 81

Expert Comment

by:byundt
ID: 41815074
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.
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

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question