Separating letters from the end into another cell

[Gautham A]

Hello
In the cell A1,it has the value abcdefghSuccess
I want to separate the last 7 letters into a another cell such that A1 will be abcdefgh and B1 will be Success.
How to do this in VBA? Thanks in advance

 

[DanteAmor]

Hi Peter, With that, the "bad characters" continue at the end in column B. I think the ideal would be to leave the word clean, in case the OP uses it for other things.

 

[Peter_SSs]

Hi Peter, With that, the "bad characters" continue at the end in column B. I think the ideal would be to leave the word clean, in case the OP uses it for other things.

Yes, I forgot to include that tidy-up, thanks.
Same assumption as in my previous post.

Sub Split_Data_v2()
  Dim SplitItem As Variant

  For Each SplitItem In Split("Success|Running", "|")   '<- Add/change words if required
    Columns("A").Replace SplitItem, "|" & SplitItem & "|", xlPart, , False
  Next SplitItem
  Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, "|", Array(Array(1, 1), Array(2, 1), Array(3, 9))
End Sub

 

[jolivanes]

No matter how many non alphabetical characters are trailing characters.

Sub Another_Way()
Dim c As Range, i As Long
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        For i = 1 To Len(c)
            If Asc(Mid(c, Len(c) - i, 1)) >= 64 And Asc(Mid(c, Len(c) - i, 1)) < 91 Or Asc(Mid(c, _
                Len(c) - i, 1)) >= 96 And Asc(Mid(c, Len(c) - i, 1)) < 123 Then
                    c.Offset(, 1).Value = Mid(c, Len(c) - i - 6, 7)
                        c.Value = Left(c, Len(c) - i - 7)
                    Exit For
                Else
            End If
        Next i
    Next c
End Sub

 

[Rick Rothstein]

@jolivanes,

Why did you include the "at" symbol (@), ASCII 64, and the "backward apostrophe" (`), ASCII 96, as valid characters in your If..Then test? Assuming you meant to do that, here is another, shorter way to write that If..Then test...

If Mid(C, Len(C) - i, 1) Like "[@-Z`-z]" Then

If you actually meant to exclude them, then this is what the If..Then test would look like...

If Mid(C, Len(C) - i, 1) Like "[A-Za-z]" Then

 

[jolivanes]

@ Rick
I am sure your smart enough to realize that that should have been 65 and 97, not the @ and apostrophe.
But thanks for catching that.

 

[jolivanes]

If Mid(C, Len(C) - i, 1) Like "[A-Za-z]" Then

Yes Rick, that is of course the way to go. Thanks again.
The main thing for me was not to have to guess what the trailing characters are and how many there are.
I think that that was accomplished.