Pull out lastname


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 12th May 2010, 21:04
WLMPilot's Avatar
WLMPilot
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Pull out lastname
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

Cell A1 = "John Doe" (without quotes)

In a macro, I have the following:
name = Range("A1").Value

How to I pull out just the last name of the person in the variable name and
place it in the variable, lastname?

Thanks,
Les



The Code Cage Advertisment
Advertisement
  #2  
Old 12th May 2010, 21:37
Brian's Avatar
Brian
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: Pull out lastname

------ Register to get rid of these "In Post" ads! ------


Formula wise, =RIGHT(A1,(FIND(" ",A1)-2)). You can work that into a macro if
need be.

"WLMPilot" wrote:

> Cell A1 = "John Doe" (without quotes)
>
> In a macro, I have the following:
> name = Range("A1").Value
>
> How to I pull out just the last name of the person in the variable name and
> place it in the variable, lastname?
>
> Thanks,
> Les




  #3  
Old 12th May 2010, 21:37
Brian's Avatar
Brian
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: Pull out lastname

------ Register to get rid of these "In Post" ads! ------




"WLMPilot" wrote:

> Cell A1 = "John Doe" (without quotes)
>
> In a macro, I have the following:
> name = Range("A1").Value
>
> How to I pull out just the last name of the person in the variable name and
> place it in the variable, lastname?
>
> Thanks,
> Les




The Code Cage Advertisment
Advertisement
  #4  
Old 12th May 2010, 21:39
Rick Rothstein's Avatar
Rick Rothstein
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Pull out lastname

------ Register to get rid of these "In Post" ads! ------


There is no fool-proof way of parsing a field where the first and last names
exist together. There are first names composed of two names (such as Mary
Anne) and last names composed of two name parts (such as Della Rossa, who is
a friend of mine; or da Vinci of Leonardo fame; or de Fermat of mathematical
fame). I can get you the last separated name (whether that is the whole last
name or not is for you to decide)...

LastName = Mid(name, InStrRev(name, " ") + 1)

--
Rick (MVP - Excel)



"WLMPilot" <WLMPilot@discussions.microsoft.com> wrote in message
news:3D1390E2-438D-4C4C-B3EB-04E08CAC6A70@microsoft.com...
> Cell A1 = "John Doe" (without quotes)
>
> In a macro, I have the following:
> name = Range("A1").Value
>
> How to I pull out just the last name of the person in the variable name
> and
> place it in the variable, lastname?
>
> Thanks,
> Les





  #5  
Old 12th May 2010, 22:55
Helmut Meukel's Avatar
Helmut Meukel
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Pull out lastname

------ Register to get rid of these "In Post" ads! ------


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im Newsbeitrag
news:uOVKkLh8KHA.4648@TK2MSFTNGP04.phx.gbl...
> There is no fool-proof way of parsing a field where the first and last names
> exist together. There are first names composed of two names (such as Mary
> Anne) and last names composed of two name parts (such as Della Rossa, who is a
> friend of mine; or da Vinci of Leonardo fame; or de Fermat of mathematical
> fame). I can get you the last separated name (whether that is the whole last
> name or not is for you to decide)...
>
> LastName = Mid(name, InStrRev(name, " ") + 1)
>
> --
> Rick (MVP - Excel)
>



Just to add some more examples:
Lois McMaster Bujold
William H. Keith Jr.
A. E. Van Vogt
Lillian Steward Carl
all the above are authors.
BTW, Lois was born as Lois McMaster
and Lillian as Lillian Steward.

Another example: DuPont's full name is
E. I. du Pont de Nemours and Company
named after it's founder Eleuthère Irénée du Pont,
a french immigrant who came from Nemours in
France. (Don't know if it refers to the city or the
duchy).

Helmut.




  #6  
Old 13th May 2010, 01:33
JLGWhiz's Avatar
JLGWhiz
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Pull out lastname

------ Register to get rid of these "In Post" ads! ------


You can use this method to return the last name, no matter how many first
and middle names are listed. This returns the last text separated by a
space.

Sub dj()
Dim lnm
lnm = Split(Range("B2"), " ", -1)
Range("B4") = lnm(UBound(lnm))
End Sub

If the name is in B2 it will put the last name in B4. You can work this
into a loop for a range of names.



"WLMPilot" <WLMPilot@discussions.microsoft.com> wrote in message
news:3D1390E2-438D-4C4C-B3EB-04E08CAC6A70@microsoft.com...
> Cell A1 = "John Doe" (without quotes)
>
> In a macro, I have the following:
> name = Range("A1").Value
>
> How to I pull out just the last name of the person in the variable name
> and
> place it in the variable, lastname?
>
> Thanks,
> Les






The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
lastname, pull

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 05:33.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.18588 seconds
  • Memory Usage 6,364KB
  • Queries Executed 17 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (3)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (6)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (6)post_thanks_postbit_info
  • (6)postbit
  • (6)postbit_ctoptip
  • (6)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (2)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php
  • ./includes/functions_prefix.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete