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

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)

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.

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

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

See example of the above attached

barry
text-parse.xls

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

Select allOpen 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

Hi,

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