• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

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

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
Alex Campbell
Asked:
Alex Campbell
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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
 
Martin LissOlder than dirtCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
Nice idea Syed.
0
 
[ fanpages ]IT Services ConsultantCommented:
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
 
Alex CampbellAuthor Commented:
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
 
Alex CampbellAuthor Commented:
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
 
Alex CampbellAuthor Commented:
The functions that were submitted didn't work for me.  I searched other sites and found a workable solution.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now