Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2011-10-12
Medium Priority
489 Views
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
0
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)
0

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)

=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

LVL 21

Expert Comment

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

LVL 23

Assisted Solution

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,LEFT(A1,lastindexof(A1,"-")-1),"")
=IF(lastindexof(A1, "-")>0,RIGHT(A1,LEN(A1)-lastindexof(A1,"-")), "")

Michael
0

LVL 9

Expert Comment

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

LVL 50

Expert Comment

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 "-"

=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

LVL 50

Expert Comment

ID: 36954692
See example of the above attached

barry
text-parse.xls
0

LVL 93

Expert Comment

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

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 Closing Comment

ID: 36992950
Hi,

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

## Featured Post

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…
###### Suggested Courses
Course of the Month20 days, 16 hours left to enroll