Dividing first & last names?

Is it possible to divide first and last names that have a space between them 
is one cell and put copy them to two separate cells?
Thanks,
Digital2k 


0
digital2k (37)
7/16/2006 12:48:34 PM
excel 39879 articles. 2 followers. Follow

10 Replies
631 Views

Similar Articles

[PageSpeed] 27

Hi Digital2k,

Data>Text to Columns  will do that admirably.

HTH
Martin 


0
mtmw (348)
7/16/2006 1:03:05 PM
Thank you Martin,
but I'm not an expert. I'm not clear on what I'm supposed to do with this.
The Full name is in column (A) I want the first name in column (B) and the 
last name in column (C).
Where would I use Data>Text at?
Digital2k

"MartinW" <mtmw@hotmail.invalid> wrote in message 
news:ugR$rgNqGHA.928@TK2MSFTNGP04.phx.gbl...
> Hi Digital2k,
>
> Data>Text to Columns  will do that admirably.
>
> HTH
> Martin
> 


0
digital2k (37)
7/16/2006 1:40:35 PM
Data > Text to columns will keep the first name in column A and move th
second to column B.

If you still want to retain the full name in column A then copy colum
A to column B, select column B and use Data menu > Text to column
option with space as delimiter.

Alternatively, using formulas, in B1

=LEFT(A1,FIND(" ",A1)-1)

and in C1

=REPLACE(A1,1,FIND(" ",A1),""

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56182

0
7/16/2006 1:58:30 PM
Hi Digital2K,

Highlight all of your names in column A.
Goto Data>Text to Columns.
In the first page of the wizard select delimited then click next.
In the second page of the wizard check space (make sure all other
options are unchecked.) then click next.
In the third page in destination put $B$1 and click finish.

These directions are for Excel 2000, if you have a different version
the process may be a little different but the basics should be the same.

HTH
Martin






"Digital2k" <digital2k@adelphia.net> wrote in message 
news:SaCdnTgUgbNA3CfZnZ2dnUVZ_rudnZ2d@adelphia.com...
> Thank you Martin,
> but I'm not an expert. I'm not clear on what I'm supposed to do with this.
> The Full name is in column (A) I want the first name in column (B) and the 
> last name in column (C).
> Where would I use Data>Text at?
> Digital2k
>
> "MartinW" <mtmw@hotmail.invalid> wrote in message 
> news:ugR$rgNqGHA.928@TK2MSFTNGP04.phx.gbl...
>> Hi Digital2k,
>>
>> Data>Text to Columns  will do that admirably.
>>
>> HTH
>> Martin
>>
>
> 


0
mtmw (348)
7/16/2006 2:02:31 PM
Select the column, go to Data>Text To Columns, click the delimited button,
check space delimiter box, and Finish

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" <digital2k@adelphia.net> wrote in message
news:SaCdnTgUgbNA3CfZnZ2dnUVZ_rudnZ2d@adelphia.com...
> Thank you Martin,
> but I'm not an expert. I'm not clear on what I'm supposed to do with this.
> The Full name is in column (A) I want the first name in column (B) and the
> last name in column (C).
> Where would I use Data>Text at?
> Digital2k
>
> "MartinW" <mtmw@hotmail.invalid> wrote in message
> news:ugR$rgNqGHA.928@TK2MSFTNGP04.phx.gbl...
> > Hi Digital2k,
> >
> > Data>Text to Columns  will do that admirably.
> >
> > HTH
> > Martin
> >
>
>


0
bob.NGs1 (1661)
7/16/2006 2:03:28 PM
Thank you to all!!
Took awhile but I'm learning thanks to this great forum.
Digital2k

"MartinW" <mtmw@hotmail.invalid> wrote in message 
news:eWuM5BOqGHA.4988@TK2MSFTNGP04.phx.gbl...
> Hi Digital2K,
>
> Highlight all of your names in column A.
> Goto Data>Text to Columns.
> In the first page of the wizard select delimited then click next.
> In the second page of the wizard check space (make sure all other
> options are unchecked.) then click next.
> In the third page in destination put $B$1 and click finish.
>
> These directions are for Excel 2000, if you have a different version
> the process may be a little different but the basics should be the same.
>
> HTH
> Martin
>
>
>
>
>
>
> "Digital2k" <digital2k@adelphia.net> wrote in message 
> news:SaCdnTgUgbNA3CfZnZ2dnUVZ_rudnZ2d@adelphia.com...
>> Thank you Martin,
>> but I'm not an expert. I'm not clear on what I'm supposed to do with 
>> this.
>> The Full name is in column (A) I want the first name in column (B) and 
>> the last name in column (C).
>> Where would I use Data>Text at?
>> Digital2k
>>
>> "MartinW" <mtmw@hotmail.invalid> wrote in message 
>> news:ugR$rgNqGHA.928@TK2MSFTNGP04.phx.gbl...
>>> Hi Digital2k,
>>>
>>> Data>Text to Columns  will do that admirably.
>>>
>>> HTH
>>> Martin
>>>
>>
>>
>
> 


0
digital2k (37)
7/16/2006 6:15:28 PM
On Sun, 16 Jul 2006 08:48:34 -0400, "Digital2k" wrote in
microsoft.public.excel:

>Is it possible to divide first and last names that have a space between them 
>is one cell and put copy them to two separate cells?

Define first and last name.

Any simple solution will not produce the required results for other than
the most simple cases. Consider: John Fitzgerald Kennedy, Jan de Beer,
Sir Peter Ustinov, Marshall Bruce Mathers III.

-- 
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"
0
ROT13-zo (57)
7/17/2006 9:18:34 AM
OK, What would be your solution is a case like your example?
Digital2k

"Michael Bednarek" <ROT13-zo@zorqanerx.pbz> wrote in message 
news:m1lmb2ljop2fud6scgic488p9f3nvu4igg@4ax.com...
> On Sun, 16 Jul 2006 08:48:34 -0400, "Digital2k" wrote in
> microsoft.public.excel:
>
>>Is it possible to divide first and last names that have a space between 
>>them
>>is one cell and put copy them to two separate cells?
>
> Define first and last name.
>
> Any simple solution will not produce the required results for other than
> the most simple cases. Consider: John Fitzgerald Kennedy, Jan de Beer,
> Sir Peter Ustinov, Marshall Bruce Mathers III.
>
> -- 
> Michael Bednarek   http://mbednarek.com/   "POST NO BILLS" 


0
digital2k (37)
7/17/2006 9:56:14 AM
Michael is just giving an advisory notice that you may have to
tweak the results.   With programming one has more control and
can write a bunch of exceptions and manually correct others.   Something
made more difficult if relying on Worksheet Formulas and/or
starting over each time with  a listing.    Examples for three or
four names is a bit rough considering that the OP said they
start with first and last names meaning no titles and somebody
presumably removed extraneous initials would leave only
compound first names and compound last names as a problem.

The obvious answer would be that CHAR(160) were included
in compound names (including III and Jr, Sr),  no titles are included,
and CHAR(160) is treated same as  space for sorting.  But it
also depends on who the list is intended for.

"Digital2k" <digital2k@adelphia.net> wrote
> OK, What would be your solution is a case like your example?


0
7/17/2006 10:48:14 AM
Thank You David!
Digital2k

"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message 
news:%23lzk07YqGHA.3820@TK2MSFTNGP05.phx.gbl...
> Michael is just giving an advisory notice that you may have to
> tweak the results.   With programming one has more control and
> can write a bunch of exceptions and manually correct others.   Something
> made more difficult if relying on Worksheet Formulas and/or
> starting over each time with  a listing.    Examples for three or
> four names is a bit rough considering that the OP said they
> start with first and last names meaning no titles and somebody
> presumably removed extraneous initials would leave only
> compound first names and compound last names as a problem.
>
> The obvious answer would be that CHAR(160) were included
> in compound names (including III and Jr, Sr),  no titles are included,
> and CHAR(160) is treated same as  space for sorting.  But it
> also depends on who the list is intended for.
>
> "Digital2k" <digital2k@adelphia.net> wrote
>> OK, What would be your solution is a case like your example?
>
> 


0
digital2k (37)
7/17/2006 11:25:17 AM
Reply:

Similar Artilces:

Charting number of "name" occurences
:confused: Can someone tell me how I can chart the number of times a name occurs in a column? I would really appreciate it. Maybe just like a pie chart that everytime a new name is added it adds it in the chart, and everytime a name is repeated it just adds to the count of the name already added. Understand??? Can someone help me? Thank you! David -- silentop ------------------------------------------------------------------------ silentop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32271 View this thread: http://www.excelforum.com/showthread.php?threadi...

naming shapes
how can i rename a shape? Hi Mel, rightclick the shape then format/special and type your new name in the Name field Ren´┐Ż "mel" <mel@discussions.microsoft.com> wrote in message news:569C336E-12D9-455B-A269-99303271DB58@microsoft.com... > how can i rename a shape? FYI: If another shape already has the name you type in, you'll get a "dot-number" after your new name, but after you close the dialog. So if there's a shape called "Decision", and you rename another shape to Decision, it will end up named "Decision.36" or something si...

New Domain name
We currently have the domain name format of dept.county.la.ca.us. We want to change it dept.countyname.gov format. We also want to keep both domain names for a certain period of time. What's the efficient way to do this? Do I just add an alias to my exchang server? Do I need to assign different IP to the alias? Thank you. -- JoeCL LACO-CAO Assuming that all your users will get a new address with the new domain name all you need to do is add the domain to the default recipient policy. If you want it to apply to only a subset of your users a recipient policy might be in order....

failed to find host name from ip address
Please help a numpty in need!! I have an account set up in outlook express 2003 which works fine. I have now set up another account, and every time i send an email wifrom the new account it comes back as undeliverable, with the following: failed to find host name from ip address Any ideas please?? "g00nor" <g00nor@discussions.microsoft.com> wrote in message news:4D1B4492-A265-4030-8390-9CCDF4A8BEBC@microsoft.com... > Please help a numpty in need!! > I have an account set up in outlook express 2003 which works fine. > I have now set up another account, and every ...

is there a "File name" function in ms Excel (2002)
Hi Anyone know of any msExcel function that will give the name of the current file? To be honest I want if for printout purposes because I am trying to avoid the custom header etc (which seems difficult/impossible to format) Shiphen Shiperton Henethe One way =LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(".", MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3) can be put in any cell if you want the full path =SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",...

Is there a way I can split last name first name in one cell
I just got a list of customers we need to write about a new store opening. However, I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another.I would of course buy the program that will do that if there is no way to change that w/o a special program. O yes yes, how about stripping the duplicates out of the list, is there a way to do that as well. I'm an old guy, that used excel for a while, but I can't remember anything anymore, honestly......

Ithaca Printer stops in the middle of last page
One problem I am trying to fix... cash transaction has no problem no matter how many items are scanned. when more than 2 items are scanned with credit card transaction; the last page of the receipt stops in the middle. so, printer prints 2 signature pages, 1 full page of receipt and 1 half page of receipt. any idea how to fix? Hardware is Ithaca610 (Ithaca 280 Driver), win xp. I am taking a guess as I have never worked with an Ithaca printer but try checking the length of the page in the printer properties. Rob "bluecomga" wrote: > One problem I am trying to fix... > ...

SBS BPA 2008: WARNING: DNS name server records do not exists
Hey everyone I get the following warning in SBS Best Practice Anaylser: "The DNS name server records do not exists. The forward lookup zone DOMAIN.local does not contain any DNS name server (NS) resource records with SERVER.DOMAIN.local for the _msdcs delegation." How can I fix this entry in dns-manager and what values do I have as standard in this tree for an SBS 2008 Server? Any help is greatly appreciated. Simon "Simon" <anonymous@discussions.microsoft.com> wrote in message = news:ueeAogczKHA.4328@TK2MSFTNGP04.phx.gbl... > Hey everyon...

How do I print only the first and last page of publication?
I need to print 500 11X17 sheets of only page 1 and 4 (first and last page) KJL <KJL@discussions.microsoft.com> was very recently heard to utter: > I need to print 500 11X17 sheets of only page 1 and 4 (first and last > page) Is this in a booklet format? Go to Page 1 File > Print > Current Page > Copies: 500 > OK > Print as separate booklet: No -- Ed Bennett - MVP Microsoft Publisher Print, current page, click "no". Try a sample first. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "K...

Local and Internet name...
Hello, I got a problem with a exchange 2003 SBS. The domain name ( let say 123.org ) is by a provider , I configured pop3 connector to pik up email, this works fine. As some 123.org users are in my lan and others around the country, I got a problem when I try to send a mail to the users that are not configured in my server. The exchange server server answer me that the user is unknown. I heve certainely configured the server as as the FQDN 123.org name mail name and this is my problem. So my question is, can I change that and where. Thank you very much for any help. Jean-Pierre ...

Automatic divided by 100000
Hi all, On a new worksheet, when I type in a number in the cell, right after I hit "Enter", it automatic divide the number by 100,000, why this happened? Please help. Thanks. Gian Tools>options>edit and uncheck fixed decimal places Regards, Peo Sjoblom "Gian" wrote: > Hi all, > > On a new worksheet, when I type in a number in the cell, right after I hit > "Enter", it automatic divide the number by 100,000, why this happened? > Please help. > > Thanks. > > Gian Thanks. "Peo Sjoblom" wrote: > Tools>...

"how to delet names from Microtype"
I run an elementary school computer lab and cannot delete names from the Microtype program that was installed previous to my employment. It is full and I cannot add more names, Help! This is a forum for Microsoft Word. I have never heard of Microtype, though Google search results suggest that it is a typing program. From its Web site, it does not appear that there are any user forums, but you can submit a question via http://www.microtype.swlearning.com/mt4/talktous/talktous.html. There is technical support at http://kb.cengage.com/display/Keyboarding/Keyboarding+Products, bu...

Pub 2003 spaces last line ont paragraph differently
If you change the Line Spacing After Paragrahs from 0 to say 6pt Publisher 2003 increases the line spacing for the last line of the pagraph. So the last line of the paragraph is a little bit further below the 2nd to last line than all of the other lines are. You can see this by creating a text box of XX's (2 paragraphs worth) and changing the Spacing After paragraph. Anyone know why this is or how to fix it? Are you using a template? -- Mary Sauer http://msauer.mvps.org/ "Mr. Analogy" <MrAnalogy@discussions.microsoft.com> wrote in message news:58FD08C2-BB40-42...

WLM 2001
Just downloaded Windows Live Mail 2011 beta, my current version now is Version 2011 (Build 15.4.3002.0810) This is a German version and i upgraded it from WLM. First impression is quite ok - the import of all eMail accounts and store folders worked. As it seems the Alt+M shortcut no longer works, this displayed a familiar drop-down menu style, does anyone know if there is still a way to do this? Next, if i understood correctly, the calendar still has no reminders, unless you are logged into some live account. Am i right or have i missed several things? greetings Ro...

Print Comany Name on Report
Is it possible to print Company Name on the Reports? Which report? Which version/edition of Money? "Sameer Jaffer" <Sameer Jaffer@discussions.microsoft.com> wrote in message news:B5786460-099F-49CD-B094-D272653A924C@microsoft.com... > Is it possible to print Company Name on the Reports? Money Small Business 2006. P&L or any report. When I see or print the report My company name is not on it.. How to get that on the report. "Dick Watson" wrote: > Which report? Which version/edition of Money? > > "Sameer Jaffer" <Sameer Jaffer@dis...

listing last & first names
i imported an access database, everything seemed to go well. but it wont list the last & first names at all. not even under "full name". im using the "detailed view" under "full name, its blank. do i delete it? i couldnt find a way to do that. thanks, francine ...

Clearing Automatic Name Checking/Suggestion
Does anyone know how to clear the memory setting for automatic name checking? When it suggests an email address, it gives me an old one or incorrect one from my outlook that is no longer in my Contacts and it's frustrating. Please advise, I've already tried turning the settings on and off with no luck. Thanks, KjL ...

make First row Header
How can I make the first row in my excel document the headers. Example: change A, B, C to Name, Phone, Address As I enter additional data I will need the forst row to stay as the header. Thanks You can't. You can use letters or numbers or nothing. Tools|options|General tab|check/uncheck R1C1 Reference style or tools|options|view tab|check/uncheck row and column headers. But there's nothing stopping you from putting headers in row 1 and then freezing panes so that row 1 is always visible. As a user of excel, I expect to see the row and column headers. So I don't see a problem...

Dividing text
I know about the text-to-columns function, but if I have a sentence of text with each word separated by a space, it'll split across as many columns as there are words in the source cell. If I want to split on a specific 3 character sequence (namely " - "), is there any way I can do so by using the various text functions to determine where in the sentence that exact sequence occurs, and splitting only at that point? -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Hi Paul As you may have discovered, the 'TextToC...

Table Name
How do I get the name of the current table? I am talking about when I use the Home>Format as Table (In the Styles section) and then format data as a table. Typically Excel assigns the name Table1, Table2, etc. How can I determine the table name in VBA? here you go ActiveSheet.ListObjects.Add(xlSrcRange, Range(ActiveCell.CurrentRegion.Address), , xlYes).Name = "ABCD1" But the only catch over here is your cursor should be with in the table. I mean if you have data in range "A1:E40", then your cursor should be with in this range. If the cursor is in cell...

Name in email
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Using EWS Entourage found name in the sent field is now email address by default rather than Office User name how can I change the sent by name to my name not my email address Just to be clear when whoever I email receives and email from me it comes from my email address not my name like normal Entourage client does On 2/2/10 3:18 PM, in article 59bb222b.-1@webcrossing.JaKIaxP2ac0, "goliath@officeformac.com" <goliath@officeformac.com> wrote: > Exchange Using EWS...

Pivot
Hi, I have a pivot with Sales information. The data has multiple columns by various product category. I have added a count column next to each product column representing the dollar amount. Is there a way in Pivot to get a calculated field of Total amount / Count for each product to arrive at Average value per deal. Excel does not allow me to use Calculated Item to get the average number. Any suggestion is welcome. Regards Sandip. If you are unable to create the formula in the pivot table, have yo considered placing it in a row next to the table? Just a thought... -- SHMUNC ---------...

Last Logon Time in Exch. Mgr./Mailboxes reset by scheduled mainten
I have been using Exchange Manager, via drilling down to the storage group's Mailboxes screen, to get a check on users who are not active (and therefore may have been excused without my knowledge). Recently, something changed without intervention (probably via a patch) where the "Last Logon Time" is being reset every morning at 2:30. I am assuming there is a maintenance cycle involved, but I do not know what. While I know there are utilities and scripts to mine AD for the needed info, I am wondering what changed and if I really need this maintenance cycle? If I cannot ...

Excel Report Builder report is deleting the last column from SQL v
I am using Excel Reports in conjunction with Smartconnect. After about a dozen smartconnect connections and refreshes of data from GP in my excel report, the original SQL view created from the Excel Report Builder deletes the last column from the SQL view. I am now unable to refresh my excel report because of an "invalid column name". The only work around at this point is to re-publish my excel report from the excel report builder tool; however, this is not the preferred method of resolution. Any other suggestions? I just had this happened - I thought someone must h...

Finding Last Record
Being very new to Access I am struggling a bit, so need some assistance. I have an ACCESS 2000 manual and keep trying to figure it out, but I am just a bit ACCESS deprived. I have a form that will be used for New Employees. When I OPEN the form, (OPEN event, I think,) I need to get to the last record in my table. I have tried DoCmd.GoToRecord acDataTable, "SalesAssociate", acLast but get an error saying the table is not open. I have tried several other things, but nothing seems to work. I know I have it wrong, but can't figure it out. Any suggestions...