0

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?

zx8754
  • 52,746
  • 12
  • 114
  • 209
user990951
  • 1,469
  • 5
  • 26
  • 36

2 Answers2

3

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,"")
Community
  • 1
  • 1
A. Webb
  • 26,227
  • 1
  • 63
  • 95
  • Looks good but the data differs in size all through the records.Sorry I didn't specify that in the beginning – user990951 Oct 24 '12 at 01:34
  • Modified question is now analogous to http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba – A. Webb Oct 24 '12 at 02:09
0

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)

Dave Lewis
  • 131
  • 1
  • 1
  • 5