Find Last Name

Version 22

    Description:

     

    This formula finds the last name in a name:

    .

    TRIM(RIGHT(REPLACE(ATTR([Name]), " ", SPACE(LEN(ATTR([Name])))), LEN(ATTR([Name]))))
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    .

    What it does is replacing the space before the last name with as many spaces as the name is long. This makes it possible to take a RIGHT of the name that only returns last name and leading spaces. The leading spaces are easy to TRIM away.

    .

     

    Formula Step by Step:

     

    This examples uses the name  {Alexander Graham Bell}.

    .

    1. Replicate as many spaces as the name is long:

      SPACE(LEN(ATTR([Name])))

      {                    }

    2. Replace spaces in the name with replicated spaces in #1:

      REPLACE( ATTR([Name]), " ", SPACE(LEN(ATTR([Name]))) )

      {Alexander                     Graham                     Bell}

    3. Get last name with leading spaces.

      RIGHT( REPLACE( ATTR([Name]), " ", SPACE(LEN(ATTR([Name]))) ), LEN(ATTR([Name])))

      {                 Bell}

    4. Last name without leading spaces:

      TRIM( RIGHT( REPLACE( ATTR([Name]), " ", SPACE(LEN(ATTR([Name]))) ), LEN(ATTR([Name]))) )

      {Bell}

    .

     

     

    Credits:

     

    This string calculation is not mine, and I am impressed by its cleverness. My work was "only" to search & find it on the web, convert it to Tableau syntax, and document it. As far as I can track its origin, the credit goes to Jerry Beaucaire for coming up with it and Joe Finkle for refining it. Also credit to  Juracy Americo  for asking the question  Find last name in a full name, because if he hadn't, I wouldn't have searched for it, and this community would not (at least not yet) be enriched with its cleverness.

    .

     

    .