Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: Retrieving the last word in a field

    Is there a function that will retrieve the last 'word' typed in a field.
    For example:

    "My name is Anna" = "Anna"
    "My father's age is 56" = "56"

    The function 'rightwords' doesn't seem to exist in Access.

    Thankyou for reading and eventually answering my question.

  2. #2
    Join Date
    Oct 2003
    Posts
    311
    try len() and instr() (not sure if im 100% correct these are from VB6 but should be something like this in exl

    or some thing liek a loop backwards

    S="Today is monday"
    i=len(s)

    for j = i to 1 step -1
    if mid() exit for ' use mid here to see if the chr is a space
    next j

    ans=right(s,j)


    Sorry didnt really have time to test this,,

    u may also be able to split the string based on spaces into an array and then just return the last item in the array,,
    i havent done that in xl either but in dot net so... im sure there is a way

    M~

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    If you are doing this in Excel (rather than Access), then try this formula.

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,A1,RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

    [Edited to note that the target cell is A1, and you can put this formula into B1. Adjust as necessary.]
    Last edited by shades; 01-12-04 at 13:05.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •