CODEWITHSUNDEEP
excelexcel-2010vba
Nitish Bangad
Nitish Bangad

Reputation: 51

Splitting cells in Excel

I want to split cells in excel on last occurence of : (Colon) . For example test:test1:test2 must be split into test2 i.e., last occurence of :.

How can I do this ?

Upvotes: 1

Views: 195

Answers (3)

DeanOC
DeanOC

Reputation: 7282

You could also write your own UDF which wraps InstrRev

Public Function InstrReverse(cell As Range, char As String) As Integer

InstrReverse = InstrRev(cell.Value, char)

End Function

Then (assuming your target string is in A1), this will give you the left part

=LEFT(A1,InstrReverse(A1,":")-1)

and this will give you the right part

=RIGHT(A1,LEN(A1)-InstrReverse(A1,":"))

Upvotes: 0

Karl-Henrik
Karl-Henrik

Reputation: 1113

If I understand you correctly you do it like this with VBA WrdArray holds all the words and we use UBound to get the last one in the array.

Public Sub Spl()
    Dim WrdArray() As String
    Dim text_string As String
    text_string = Sheet1.Cells(2, 2)
    WrdArray() = Split(text_string, ":")
    x = WrdArray(UBound(WrdArray()))
    Sheet1.Cells(2, 3) = x
End Sub

Thanks to simoco for pointing out that the original function didn't work.

=RIGHT(B2; SEARCH(":";B2;1))

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

Assuming that your substring after last : is less than 255 characters:

=TRIM(RIGHT(SUBSTITUTE(B2,":",REPT(" ",255)),255))

if your substring after last : could be longer than 255 characters:

=RIGHT(B2,LEN(B2)-MATCH(2,1/(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)=":")))

both formulas with array entry (CTRL+SHIFT+ENTER)

Upvotes: 1

Related Questions