I'm trying to separate the last name from the first name in a field. I can
get the first name by using the "left" function. Does anyone know how to get
the last name? I tried using "right" function but on some it gives me the
whole last name plus part of the first name, others it gives me some of the
letters of the last name. Please advise - thanks!
|
|
0
|
|
|
|
Reply
|
 Utf
|
4/15/2010 8:57:01 PM |
|
Karen, Have a look at using <data> <text to columns> You van use the space
as a separator.
--
Greetings from New Zealand
"Karen D" <KarenD@discussions.microsoft.com> wrote in message
news:37113207-F5A3-4A6C-831E-F5AD54203D90@microsoft.com...
> I'm trying to separate the last name from the first name in a field. I
> can
> get the first name by using the "left" function. Does anyone know how to
> get
> the last name? I tried using "right" function but on some it gives me the
> whole last name plus part of the first name, others it gives me some of
> the
> letters of the last name. Please advise - thanks!
|
|
|
0
|
|
|
|
Reply
|
Bill
|
4/15/2010 9:04:01 PM
|
|
This won't work for everything (Jr., Sr., De La Hoya, etc.),
but should work for most:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Karen D" <KarenD@discussions.microsoft.com> wrote in message
news:37113207-F5A3-4A6C-831E-F5AD54203D90@microsoft.com...
> I'm trying to separate the last name from the first name in a field. I
> can
> get the first name by using the "left" function. Does anyone know how to
> get
> the last name? I tried using "right" function but on some it gives me the
> whole last name plus part of the first name, others it gives me some of
> the
> letters of the last name. Please advise - thanks!
|
|
|
0
|
|
|
|
Reply
|
RagDyer
|
4/15/2010 11:14:24 PM
|
|
You should have posted few examples. I assume the firstname and last name is
separated by a space
For example to extract 'Karen' from 'Karen D' you can use
=LEFT(A1,FIND(" ",A1)-1)
But if you have names without lastnames then modify the formula as below
A1 = 'Karen D'
B1=LEFT(A1 & " ",FIND(" ",A1 & " ")-1)
C1=MID(A1,LEN(B1)+2,255)
--
Jacob (MVP - Excel)
"Karen D" wrote:
> I'm trying to separate the last name from the first name in a field. I can
> get the first name by using the "left" function. Does anyone know how to get
> the last name? I tried using "right" function but on some it gives me the
> whole last name plus part of the first name, others it gives me some of the
> letters of the last name. Please advise - thanks!
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/16/2010 5:28:01 AM
|
|
|
3 Replies
250 Views
(page loaded in 0.061 seconds)
Similiar Articles: Function similar to "LEFT" - microsoft.public.excel ...I'm trying to separate the last name from the first name in a field. I can get the first name by using the "left" function. Does anyone know how t... Left Function - microsoft.public.excel.miscFunction similar to "LEFT" - microsoft.public.excel ... I'm trying to separate the last name from the first name in a field. I can get the first name by using the "left ... What is the meaning of the "$" in say the left$ function ...Jet SQL functions - microsoft.public.access.queries Function similar to "LEFT" - microsoft.public.excel ... I can get the first name by using the ... Get Access Database Schema - Primary Keys and Indexes - microsoft ...Is there a function similar to Allen Browne's tableInfo that will pull in primary key and ... As DAO.index Set db = CurrentDb For Each tdf In db.TableDefs If Left ... if I know the last name can I get the first name from the full ...Function similar to "LEFT" - microsoft.public.excel ... I'm trying to separate the last name from the first name in a field. I can get the first name by using the "left ... Counting Filtered Results Using Autofilter - microsoft.public ...... the number of results appears in a bar at the lower left hand corner of the Excel window, similar to ... Using the Median function with Auto-filter - microsoft.public ... ... Multi-word string from one field to two fields - microsoft.public ...... want to separate the information into two fields (similar ... ... comma, you could use combinations of the InStr, Left ... you can do in excel using the "Text to Columns" function). Jet SQL functions - microsoft.public.access.queries... this is for AC 2002/2003...hopefully 2000 will be similar ... -in the left listbox of the 3 lower listbox, expand ... function in the right list box -Select the function name ... Prevent Outlook Calendar from deleting old information - microsoft ...I was unaware that when performing the archive function it ... Along similar lines....is there anyway to have Outook ... as well - apparently after 6-months you are left ... Validate the format of a number - microsoft.public.excel ...This formula =AND(LEN(A1)=11,CODE(LEFT(A1))>64,CODE ... To enter this User Defined Function (UDF), <alt-F11> opens ... indicate 'invalid' next to it or >> something similar ... Function similar to "LEFT" - microsoft.public.excel ...I'm trying to separate the last name from the first name in a field. I can get the first name by using the "left" function. Does anyone know how t... Thomae's function, a self-similar function over the rational ...Figure 1: Thomae's function, a self-similar function over the rational numbers in the unit interval (top left). From Fractal-like Distributions over the Rational ... Save time by using Excel's Left, Right, and Mid string functions ...Don't re-key something you can extract using an Excel string function. Learn to use the "big three" string functions. Left Function Microsoft Access - TutCity - Free Tutorials DirectoryMicrosoft Access: In Access, the Left function extracts a substring from a string, starting from the left-most character. VB's Left, Right, Mid Functions in C++? | DaniWebVB has the Left(), Right(), and Mid() Functions. Is there something similar ... ... I know this, on the first line of the code, Len function is ... 7/28/2012 6:00:10 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
39 followers. 