Extract the last substring from a cell

Question

I have names in a column   I need to split just the last names from that column into another column    The last name is delimited by a space from the right side   The contents in cell A2   Alistair Stevens and I entered the formula in cell B2  I need  Stevens  in cell B2   I tried using the following formulas      RIGHT A2 FIND     A2 1 -1    RIGHT A2 FIND     A2     Both these formulas work for this cell but when I fill it down   copy and paste it for the cells below it doesn t work  I get the wrong values    A3 - gt  David Mckenzie  B3 - gt  Mckenzie

User · Answer

Try this function in Excel   Public Shared Function SPLITTEXT Text As String  SplitAt As String  ReturnZeroBasedIndex As Integer  As String         Dim s   As String   Split Text  SplitAt          If ReturnZeroBasedIndex  lt   s Count - 1 Then             Return s ReturnZeroBasedIndex          Else             Return            End If     End Function   You use it like this   First Name  A1    Last Name  A2   Value in cell A1   Michael Zomparelli   I want the last name in column A2    SPLITTEXT A1       1    The last param is the zero-based index you want to return  So if you split on the space char then index 0   Michael and index 1   Zomparelli  The above function is a  Net function  but can easily be converted to VBA

User · Answer

Simpler would be   TRIM RIGHT SUBSTITUTE TRIM A2      REPT     99   99    You can use A2 in place of TRIM A2  if you are sure that your data doesn t contain any unwanted spaces   Based on concept explained by Rick Rothstein  http   www excelfox com forum showthread php 333-Get-Field-from-Delimited-Text-String  Sorry for being necroposter

User · Answer

Right A1  Len A1 -Find   asterisk   Substitute A1    space     asterisk   Len A1 -Len Substitute A1   space      no space          Try this  Hope it works

User · Answer

RIGHT return whatever number of characters in the second parameter from the right of the first parameter  So  you want the total length of your column A - subtract the index  which is therefore    RIGHT A2  LEN A2 -FIND      A2  1     And you should consider using TRIM A2  everywhere it appears

User · Answer

Try this    RIGHT TRIM A2  LEN TRIM A2  -FIND     TRIM A2      I was able to copy paste the formula and it worked fine   Here is a list of Excel text functions  which worked in May 2011  and but is subject to being broken the next time Microsoft changes their website    -   You can use a multiple-stage-nested IF   functions to handle middle names or initials  titles  etc  if you expect them  Excel formulas do not support looping  so there are some limits to what you can do

User · Answer

Try this   Right RC -1  Len RC -1  -InStrRev RC -1

User · Answer

If you want to get the second to last word in a text  you can use this macro as a function in your spreadsheet   Public Function Get2ndText S As String  As String  Dim sArr   As String Dim i As Integer sArr   Split S         get the next to the last string i   UBound sArr  - 1 Get2ndText   sArr i   End Function   Then in your spreadsheet B1 as the text   CURRENT OWNER 915 BROADWAY ST HOUSTON TX 77012-2126   in B2 your formula would be    Get2ndText B1    The result would be  TX

User · Answer

This works  even when there are middle names    MID A2 FIND CHAR 1  SUBSTITUTE A2     CHAR 1  LEN A2 -LEN SUBSTITUTE A2            1 LEN A2     If you want everything BUT the last name  check out this answer   If there are trailing spaces in your names  then you may want to remove them by replacing all instances of A2 by TRIM A2  in the above formula    Note that it is only by pure chance that your first formula  RIGHT A2 FIND     A2 1 -1  kind of works for Alistair Stevens  This is because  Alistair  and   Stevens  happen to contain the same number of characters  if you count the leading space in   Stevens

User · Answer

The answer provided by  Jean provides a working but obscure solution  although it doesn t handle trailing spaces   As an alternative consider a vba user defined function  UDF   Function RightWord r As Range  As Variant     Dim s As String     s   Trim r Value      RightWord   Mid s  InStrRev s         1  End Function   Use in sheet as  RightWord A2

[excel] Extract the last substring from a cell

Examples related to excel

Examples related to excel-formula

Examples related to excel-2007