CODEWITHSUNDEEP
excelexcel-2007excel-2010
user990951
user990951

Reputation: 1479

Delete Certain Characters in Excel 2010

I have ~4000 records of data in Excel 2010 that as below:

QW3-WE-2232-322-A-2
WETE-33-3REE-33-WEWE-3
DEE-3YED-3432-DXSDDD-3FESE-2
343-34SE-34323-AWBE-3
ABDC-343-3THYT-2

I want the data to look like this where everything after the last dash is deleted.

QW3-WE-2232-322-A
WETE-33-3REE-33-WEWE
DEE-3YED-3432-DXSDDD-3FESE
343-34SE-34323-AWBE
ABDC-343-3THYT

Was wondering if there was an easy way to do this Excel?

Upvotes: 0

Views: 15703

Answers (2)

A. Webb
A. Webb

Reputation: 26446

Answering the original question where at most two "-" separators occur:

=LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)

Answering the modified question where an arbitrary number of "-" separators occur:

=LEFT(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)

This answer is via How can I perform a reverse string search in Excel without using VBA?, where several adaptable options may be found as well.

This is very clever, but with a little VBA a more straightforward answer could be devised. The VBA to do this can be very simple, but I recommend bringing in Regular Expressions for more flexible solution you can use on other similar problems.

Function RegExReplace(pattern As String, sourceString As String, replaceVar As String) As String
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
    RE.pattern = pattern
    RE.Global = True
    RegExReplace = RE.Replace(sourceString, replaceVar)
End Function

Then in your worksheet you would use

=RegExReplace("-[^-]*$",A1,"")

Upvotes: 3

Dave Lewis
Dave Lewis

Reputation: 131

It looks to me like you just want to get rid if the last two characters at the end of the string. If so then you could try =Left(A1,Len(A1)-2)

Upvotes: 0

Related Questions