Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-12
Medium Priority
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 18

Expert Comment

ID: 36954202
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

Michael Fowler earned 2000 total points
ID: 36954391
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 21

Expert Comment

ID: 36954399

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.


Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler earned 2000 total points
ID: 36954419
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


Expert Comment

ID: 36954422
Asume there are 3 "-"
LVL 50

Expert Comment

by:barry houdini
ID: 36954674
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
ID: 36954692
See example of the above attached

LVL 93

Expert Comment

by:Patrick Matthews
ID: 36954779
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

ID: 36992950

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

864 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