CODEWITHSUNDEEP
vbaexcel
hardy
hardy

Reputation: 557

Remove or add text after a specific character in Excel

I have lot of data in my excel sheet , i want to remove all text after Last (-) character . Here is sample data like in my sheet

"This-is-car-44"
"This-is-my-school-ok"

I want look like this 

 "This-is-car"
 "This-is-my-school"

i want to remove all text after lats - , so is their any formula to do this. and one thing more if possible can i do like this in excel 

 "This-is-car-44"
    "This-is-my-school-ok"

to look like this

  "This-is-car/"
    "This-is-my-school/"

i mean after last - remove all text and add this / in end . thanks.

Upvotes: 1

Views: 3709

Answers (2)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

If you are OK with excel formulas, please try this,

=SUBSTITUTE(A1,"-"&TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1))),"/")

enter image description here

Upvotes: 3

Moosli
Moosli

Reputation: 3275

You can do something like this.

Sub RemoveLastStingPart()
Dim rng As Range
Dim intLastRow As Long
Dim strTemp As String
Dim aryTemp() As String




With ActiveSheet

    intLastRow = .UsedRange.Rows.Count
    Set rng = .Range(Cells(1, 1), Cells(intLastRow, 1))

    For Each cell In rng
        strTemp = cell.Value
        aryTemp = Split(strTemp, "-")
        strTemp = ""
        For i = 0 To UBound(aryTemp) - 1
            strTemp = strTemp & aryTemp(i) & "-"
        Next i
        strTemp = Left(strTemp, Len(strTemp) - 1)
        cell.Offset(0, 1).Value = strTemp
    Next cell


End With

End Sub

Upvotes: 0

Related Questions