How to get last string after last dash "-" and separte string into 2 separate columns

Posted on 2011-10-12
Last Modified: 2012-05-12

I have an excel column in below format


Now I want to separte this column into 2 columns. First column will have text upto last "-"
i.e ABC-1111-XYZ-
and 2nd column after last "-"
i.e 99999
Question by:baberamin
    LVL 16

    Expert Comment

    is the size fixed?  ie are the last digits always 5?
    if so you can use the following:
    Assuming A1 = ABC-1111-XYZ-99999
    B1 is the first part and  C1 is the second part use:
    B1 = left(A1;len(A1)-5)
    C1 = right(A1;len(A1)-5)
    LVL 23

    Accepted Solution

    The add the attached function to your workbook and use the formulas (assumes value is in cell A1)


    I have attached an example workbook to see it in action

    Function LastIndexOf(testString As String, findChar As String)
       Dim i As Long
       LastIndexOf = -1
       For i = 1 To Len(testString)
          If Mid(testString, i, 1) = findChar Then LastIndexOf = i
    End Function

    Open in new window

    LVL 12

    Expert Comment


    If the string is variable in length, you can use this more generic solution (assumes the original string is in A1):

    Left side = LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),3)))

    Right side = RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),3)))

    The "-" is the character you are looking for, and the 3 is telling it to find the third occurance.


    LVL 23

    Assisted Solution

    Note - the function returns -1 if the test character is not found so you could use something like

    =IF(lastindexof(A1, "-")>0,RIGHT(A1,LEN(A1)-lastindexof(A1,"-")), "")

    To output nothing when the test character is not found

    LVL 9

    Expert Comment

    Asume there are 3 "-"
    LVL 50

    Expert Comment

    by:barry houdini
    Assuming that the number of "-"s is variable and data is in A2 then this formula in B2 will give everything up to and including last "-"


    then in C2 this formula to get the rest


    regards, barry
    LVL 50

    Expert Comment

    by:barry houdini
    See example of the above attached

    LVL 92

    Expert Comment

    by:Patrick Matthews
    Another take on it, enabling VBA's Split functionality in Excel...

    1) Add this UDF to a regular VBA module:

    Function RetrieveSplitItem(Index As Long, Expression As Variant, Optional Delimiter As String = " ", _
        Optional Limit As Long = -1, Optional Compare As VbCompareMethod = vbTextCompare, _
        Optional FailIfNoDelimiter As Boolean = False) As Variant
        Dim arr As Variant
        RetrieveSplitItem = CVErr(9)
        If Not (InStr(1, Expression, Delimiter) = 0 And FailIfNoDelimiter) Then
            arr = Split(Expression, Delimiter, Limit, Compare)
            Select Case Index
                Case 0
                    RetrieveSplitItem = arr
                Case Is > 0
                    If (Index - 1) <= UBound(arr) Then
                        RetrieveSplitItem = arr(Index - 1)
                    End If
                Case Else
                    If (UBound(arr) + Index + 1) >= 0 Then
                        RetrieveSplitItem = arr(UBound(arr) + Index + 1)
                    End If
            End Select
        End If
    End Function

    Open in new window

    2) Assuming your first entry is in A2, then use this in B2 to get everything up to and including the last "-":


    3) Use this in C2 to get what's after the last dash:


    For that UDF, the Index value controls which item gets returned:

    0 returns the full array
    >0 returns the nth element, starting at 1
    <0 returns the nth to last element

    Author Closing Comment


    thanks to all of you who replied. I tried solution by Michael74 and they worked for me.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Microsoft OneNote 2010 Printer Missing 2 24
    Posting V12 2 16
    Search multiple lines 3 26
    exporting issues in Access 2 10
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now