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

Hi,

I have an excel column in below format

ABC-1111-XYZ-99999

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
baberaminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xtermieCommented:
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)
0
Michael FowlerSolutions ConsultantCommented:
The add the attached function to your workbook and use the formulas (assumes value is in cell A1)

=RIGHT(A1,LEN(A1)-lastindexof(A1,"-"))
=LEFT(A1,lastindexof(A1,"-")-1)

I have attached an example workbook to see it in action

Michael
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
   Next
End Function

Open in new window

example.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlanConsultantCommented:
Hi,

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.

HTH,

Alan.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

To output nothing when the test character is not found

Michael
0
chwong67Commented:
Asume there are 3 "-"
A1=ABC-1111-XYZ-99999
B1=LEFT(A1,SEARCH("-",A1,SEARCH("-",A1,(SEARCH("-",A1))+1)+1))
C1=RIGHT(A1,LEN(A1)-LEN(B1))
0
barry houdiniCommented:
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 "-"

=LEFT(A2,LOOKUP(2^15,FIND("-",A2,ROW(INDIRECT("1:"&LEN(A2))))))

then in C2 this formula to get the rest

=SUBSTITUTE(A2,B2,"",1)

regards, barry
0
barry houdiniCommented:
See example of the above attached

barry
text-parse.xls
0
Patrick MatthewsCommented:
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 "-":

=LEFT(A2,LEN(A2)-LEN(C2))

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

=RetrieveSplitItem(-1,A2,"-")


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
0
baberaminAuthor Commented:
Hi,

thanks to all of you who replied. I tried solution by Michael74 and they worked for me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.