Separating letters from the end into another cell

Gautham A

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

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
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?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,033
Office Version
2016
Platform
Windows
If always the last 7 letters :
VBA Code:
Sub v()
[B1] = Right([A1], 7)
[A1] = Left([A1], Len([A1]) - 7)
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
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
 

Gautham A

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
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
 

Gautham A

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
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
 

Gautham A

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,241
Messages
5,485,588
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top