Solved

How to adapt this custom Excel 2010 function to use for other character than just dash?

Posted on 2016-10-28
8
33 Views
Last Modified: 2016-11-04
I had this question after viewing Would like this function to be made to work like a MID function, but in reverse..

Function GetDash(r As Range) As String
        Dim intPos As Integer
        intPos = InStrRev(r, "-")
        GetDash = Mid(r, intPos - 1)
End Function
0
Comment
Question by:Alex972
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41864184
Function GetDash(r As Range, s as string) As String
        Dim intPos As Integer
        intPos = InStrRev(r, s)
        GetDash = Mid(r, intPos - 1)
End Function


and call it like

=getdash(a1,"@")
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41864185
Function GetDash(r As Range) As String
        Dim intPos As Integer
        intPos = InStrRev(r, "-")
        GetDash = Mid(r, intPos - 1)
End Function

Open in new window

Change the "-" in line 3 to something else or
Function GetDash(r As Range) As String
        Dim intPos As Integer
        Dim strChar As String

        strChar = Inputbox("Enter desired character")
        intPos = InStrRev(r, strChar)
        GetDash = Mid(r, intPos - 1)
End Function

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41864207
You can improve this to

Function GetDash(r As Range, Optional s As String = "-") As String
        Dim intPos As Integer
        intPos = InStrRev(r, s)
        GetDash = Mid(r, intPos - 1)
End Function


so dash (-) is the default value if the second argument is not given
1
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41864210
Nice idea Syed.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41864231
Note: If the String variable s does not exist within String variable r &, hence, intPos will be zero (0), then the following statement will produce a run-time error:

GetDash = Mid(r, intPos - 1)
0
 

Author Comment

by:Alex972
ID: 41864424
I added these different functions to a module where I have a working function.
I tried the dash and other values, but I only get #VALUE! or blank.
0
 

Accepted Solution

by:
Alex972 earned 0 total points
ID: 41866107
I appreciate the solutions suggested, but I searched elsewhere and found this:

Function Reverse(str As String) As String
    Reverse = StrReverse(Trim(str))
End Function

I like this because it can be used with MID, REPLACE and SUBSTITUTE.

A1 formula =reverse(MID(reverse(B1),1,FIND("-",reverse(B1))-2))
A1 results   =Formulas > Formula Auditing > Trace Precedents      

B1 value     =Show arrows that indicate what cells affect the value of the currently selected cell. - Formulas > Formula Auditing > Trace Precedents
reverse-mid.jpg
0
 

Author Closing Comment

by:Alex972
ID: 41873682
The functions that were submitted didn't work for me.  I searched other sites and found a workable solution.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now