Remove or add text after a specific character in Excel

Question

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.

Hi what have you tried so far? Maybe this can help. http://stackoverflow.com/questions/43818241/excel-vba-function-to-remove-letters-at-end-of-string/43818460#43818460 — Moosli, May 11 '17 at 07:12
@Moosli hi , thanks for reply , i tried text to column tool , search and replace tool , formula find stings " but not got result what i want . try google and other forums lot of search but nothing help me , im new to excel so in last i post it here may be got help . thanks but i dont understand answer given on above link . thanks — hardy, May 11 '17 at 07:16
Ok. what do you not understand? And what did you try so far? You could use aswell the Split() Method — Moosli, May 11 '17 at 07:18
Also, [here's a non VBA answer](http://stackoverflow.com/q/350264/6609896) to get the last word in the string, you could easily then use `LEFT(A1, LEN(A1)-LEN(*last word in A1*))` to achieve the desired results (where `A1` contains your data) — Greedo, May 11 '17 at 09:23

score 3 · Accepted Answer · answered May 11 '17 at 18:06

3

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

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

answered May 11 '17 at 18:06

Gowtham Shiva

3,802
2
11
27

@hardy Thats Great!! Please upvote if you found this useful. – Gowtham Shiva May 12 '17 at 12:06
yes i tried but my points not enough for upvote . when i will reach on that rank will vote for sure . – hardy May 16 '17 at 10:35
@hardy Thank you !! – Gowtham Shiva May 16 '17 at 15:33
i dont know why someone give negative vote ( down vote ) to my question . so this downgrade my points , when anyone voteup my question i got points and if then im able to vote i will do for sure . thanks – hardy May 17 '17 at 12:37

score 0 · Answer 2 · answered May 11 '17 at 07:29

0

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

answered May 11 '17 at 07:29

Moosli

3,140
2
19
45

thanks dear , one last question before run on lot of data i want to know that this can remove all text after last - or add / in last . anyways thanks again thanks for help – hardy May 11 '17 at 07:30
it will remove at every string the text after the last "-". If you want to add "/" you can add it at the Point when im Writing the value back to the Cell – Moosli May 11 '17 at 07:45

Remove or add text after a specific character in Excel

2 Answers2