Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In H4, I have a name, example MR A N OTHER. Using VBA, How can I extract the surname into H5?

    Range("H5") = Range H4 after the last space?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Range("H5") = Mid(Range("H4"), InStrRev(Range("H4"), " ") + 1)

  3. #3

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A slight problem.

    Due to the way the data is picked up, there can be spaces after the surname which I need to ignore, so I need to find the last space prior to a genuine character. Is this possible?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Range("H5") = Mid(trim(Range("H4")), InStrRev(trim(Range("H4")), " ") + 1)

    Steve

  6. #6

Posting Permissions

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