Results 1 to 4 of 4
  1. #1
    wbaroudi is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10

    Find the penultimate word in a string

    Hi guys,

    My first post.

    I wonder if someone can help.

    Beech House Dental Practice (for Night Dental), 2 North Road, Ripon, HG4 1HN

    The above is a name of diary in the cloud which is downloaded as a csv. In order to report to the NHS commissioners by location city or town I need to extract the text before the last comma and it could be one work or 2.



    I have extracted the first comma
    Surgery: Trim(Left([Calendar],[First Comma]-1))
    Also the last comma which is the Post code but I can't for the life of me figure out how to get the town/City.

    Thank you for looking
    Wessam

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would look at it from the end of the string, not the beginning of the string because the company name (presumably) could have a comma in it.

    If you break it down you can get the name of the town by doing this:

    Town: Trim(Mid([calendar],(InStrRev([calendar],",",InStrRev([calendar],",")-1)+1),(InStrRev([calendar],","))-(InStrRev([calendar],",",InStrRev([calendar],",")-1)+1)))

  3. #3
    wbaroudi is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10
    Thank you very much, you made my weekend.

    Regards
    Wessam

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    mark it solved!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find a string within a string
    By cebrower in forum Access
    Replies: 4
    Last Post: 05-14-2019, 08:12 AM
  2. RegExp to Find String at END of a String
    By Micron in forum Programming
    Replies: 3
    Last Post: 01-17-2018, 09:43 PM
  3. Replies: 10
    Last Post: 05-16-2016, 11:17 AM
  4. Find a record that contains a word in a string
    By knowshowrosegrows in forum Queries
    Replies: 2
    Last Post: 11-17-2015, 11:35 AM
  5. Split string when specific word is found
    By DB4284 in forum Programming
    Replies: 1
    Last Post: 11-18-2010, 03:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums