# 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
###### 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.

Commented:
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
Solutions 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
``````
example.xls
0

Experts Exchange Solution brought to you by

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

ConsultantCommented:
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
Solutions 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,"-")), "")

Michael
0
Commented:
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
Commented:
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
Commented:
See example of the above attached

barry
text-parse.xls
0
Commented:
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
``````

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
Author 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.