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

 

[Gautham A]

Hi Rick
I tried this one as well,still there is no change in the output.

 

[Rick Rothstein]

Then you are going to have to post your workbook on a free file sharing site (I would strongly recommend DropBox as it is a proven safe website) so that we can see exactly what your data looks like.

 

[Gautham A]

Hi Rick,

I'm not sure how to upload in Dropbox. Let me know the steps please

 

[DanteAmor]

Here is another macro for you to consider

Sub test5()
  Dim c As Range, i As Long, j As Long, lr As Long
  '
  lr = Range("A" & Rows.Count).End(3).Row
  ReDim a(1 To lr, 1 To 2)
  For Each c In Range("A1:A" & lr)
    i = i + 1
    a(i, 1) = c
    j = InStrRev(c, "Success", , vbTextCompare)
    If j = 0 Then j = InStrRev(c, "Running", , vbTextCompare)
    If j > 0 Then
      a(i, 1) = Left(c, j - 1)
      a(i, 2) = Mid(c, j, 7)
    End If
  Next c
  Range("A1").Resize(UBound(a), 2).Value = a
End Sub

 

[Gautham A]

Hi Dante,

This works. This separates the last 7 letters into the next cell. Thank you so much.
One final question, What if I have "Starting" instead of "Running".

 

[DanteAmor]

One final question, What if I have "Starting" instead of "Running".

give me a moment

Ok. Try this

Sub test6()
  Dim a As Variant, i As Long, j As Long, n As Long
  '
  a = Range("A1:B" & Range("A" & Rows.Count).End(3).Row).Value2
  For i = 1 To UBound(a, 1)
    j = InStrRev(a(i, 1), "Success", , vbTextCompare)
    If j > 0 Then
      n = 7
    Else
      j = InStrRev(a(i, 1), "Starting", , vbTextCompare)
      If j > 0 Then n = 8
    End If
    If j > 0 Then
      a(i, 2) = Mid(a(i, 1), j, n)
      a(i, 1) = Left(a(i, 1), j - 1)
    End If
  Next i
  Range("A1").Resize(UBound(a), 2).Value = a
End Sub

 

[Gautham A]

This code works like a gem. Thank you so much Dante. Was struggling for a week to get rid of this. Also appreciate Rick for his efforts.

 

[Rick Rothstein]

I was so locked into the method I chose to attack your problem with that it never occurred to me to step back and try a different approach. Kudos to Dante for doing so.

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

What about one more attempt at splitting the whole column at once with text to columns?
I've assumed that Success, Running or whatever the words are will occur at most once in a cell.

Sub Split_Data()
  Dim SplitItem As Variant

  For Each SplitItem In Split("Success|Running", "|")   '<- Add/change words if required
    Columns("A").Replace What:=SplitItem, Replacement:="|" & SplitItem, LookAt:=xlPart, MatchCase:=False
  Next SplitItem
  Columns("A").TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
End Sub