How to search a cell for last instance of repeating character anddelete to end of data

[GregInMI]

I just got hit with a hot request and am looking for commands/script
that will search the text in a cell, find the last occurance of a
repeating character and then delete the rest of the data in the cell
from that point to the end.

Here is a sample of the data I have to work with:

xxxx-xxx-xx-xxx-xxxxxxx

I want to scan to the last '-' in the cell and then delete the '-'
along with the 7 'x' characters.

Any help to leverage something that already exists versus writing a
script would be appreciated.

Regards,
Greg

 

[merjet]

Sub Macro1()
Dim c As Range
Set c = Range("A1")
For iCt = Len(c) To 1 Step -1
If Mid(c, iCt, 1) = "-" Then
c = Left(c, iCt - 1)
Exit For
End If
Next iCt
End Sub

Hth,
Merjet

 

[Lazzzx]

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

Note: The original textstring cannot include "*". If that is the case, you
will have to replace the two asterixes with som string that does not occur
in your original strings. Only works if the string to search for (here "-")
has the length of one character.

rgds,
Lazzzx

 

[GregInMI]

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

Note: The original textstring cannot include "*". If that is the case, you
will have to replace the two asterixes with som string that does not occur
in your original strings. Only works if the string to search for (here "-")
has the length of one character.

rgds,
Lazzzx

"GregInMI" <[email protected]> skrev i meddelelsen






- Show quoted text -

AWESOME! Thanks everyone for the help. I used option #2 and it
worked great!