Separating letters from the end into another cell

Gautham A

Board Regular
Joined
May 25, 2020
Messages
107
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Is it always the last seven characters or is it the last proper formatted word (first letter upper case, remaining letters lower case) no matter how many letters are in it?
 
Upvote 0
If always the last 7 letters :
VBA Code:
Sub v()
[B1] = Right([A1], 7)
[A1] = Left([A1], Len([A1]) - 7)
End Sub
 
Upvote 0
Well, if it is really the last 7 letters, and if it really is only one cell (I was kind of thinking the OP had a column of these), then this one-liner will also work...
VBA Code:
Sub v2()
  [A1:B1] = Split([REPLACE(A1,LEN(A1)-6,0,"|")], "|")
End Sub
 
Upvote 0
Hi all ,it is always the last 7 letters and they will be either Success or Running. But it is not the only cell in column I will have many rows in a column
 
Upvote 0
Give this macro a try then...
VBA Code:
Sub v3()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",REPLACE(@,LEN(@)-6,0,""|""))", "@", .Address))
    .TextToColumns , xlDelimited, , , False, False, False, False, True, "|"
  End With
End Sub
 
Upvote 0
Hi Rick
I have tried your code. But it is not giving the expected result. I have extracted this data from the web. So there are blank spaces at the end.
And also after getting data from web I have pasted this in a notepad file and I have imported in Excel using Data->Get external Data-> From text
 
Upvote 0
If those are "real" spaces (as opposed to "non-breaking" spaces which sometimes comes into a file taken from the web), then this modification to my original code should work for you...
VBA Code:
Sub v3()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF(TRIM(@)="""","""",REPLACE(TRIM(@),LEN(TRIM(@))-6,0,""|""))", "@", .Address))
    .TextToColumns , xlDelimited, , , False, False, False, False, True, "|"
  End With
End Sub
 
Upvote 0
Hi Rick
I tried this one as well but no change in the output. I'm still having blank spaces at the end which results in wrong output. I feel they must be non breaking spaces as you told
 
Upvote 0
Then your blank spaces are probably non-breaking space, so let's try this code...
VBA Code:
Sub v3()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace Chr(160), " ", xlPart, , , , False, False
    .Value = Evaluate(Replace("IF(TRIM(@)="""","""",REPLACE(TRIM(@),LEN(TRIM(@))-6,0,""|""))", "@", .Address))
    .TextToColumns , xlDelimited, , , False, False, False, False, True, "|"
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,718
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top