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
9
Medium Priority
?
489 Views
Last Modified: 2012-05-12
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
Comment
Question by:baberamin
9 Comments
 
LVL 18

Expert Comment

by:xtermie
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

by:
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

Open in new window

example.xls
0
 
LVL 21

Expert Comment

by:Alan
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
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,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
 
LVL 9

Expert Comment

by:chwong67
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

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

=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

by:barry houdini
ID: 36954692
See example of the above attached

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

=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

by:baberamin
ID: 36992950
Hi,

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

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