Separating a name into first name and last name columns

I have a series of full names in single cells that are formatted the 
following way:
<Last Name>,<Space><First Name> - thus it looks like the following:
ADAMS, ROSEMARY

I would like to separate this into two columns and eliminate the comma.  I 
appreciate any help.  I am working in Microsoft Excel 2000 and need answers 
in Excel, not in Visual Basic.

Thank you,
THEFALLGUY
0
9/7/2005 12:08:36 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
486 Views

Similar Articles

[PageSpeed] 12

David(s) - thank you for the help.  Problem solved.

The Davids stick together!!!!

Thanks,
David

"THEFALLGUY" wrote:

> I have a series of full names in single cells that are formatted the 
> following way:
> <Last Name>,<Space><First Name> - thus it looks like the following:
> ADAMS, ROSEMARY
> 
> I would like to separate this into two columns and eliminate the comma.  I 
> appreciate any help.  I am working in Microsoft Excel 2000 and need answers 
> in Excel, not in Visual Basic.
> 
> Thank you,
> THEFALLGUY
0
9/7/2005 12:06:12 AM
Data->Text To Columns->Choose "Delimited", hit next, and select "Comma"

-- 
Regards,
Dave


"THEFALLGUY" wrote:

> I have a series of full names in single cells that are formatted the 
> following way:
> <Last Name>,<Space><First Name> - thus it looks like the following:
> ADAMS, ROSEMARY
> 
> I would like to separate this into two columns and eliminate the comma.  I 
> appreciate any help.  I am working in Microsoft Excel 2000 and need answers 
> in Excel, not in Visual Basic.
> 
> Thank you,
> THEFALLGUY
0
9/7/2005 12:11:48 AM
Or you could use these formulas (assuming the name is in cell A1):

B1 =LEFT(A1,FIND(",",A1,1)-1)
C1 =RIGHT(A1,LEN(A1)-FIND(",",A1,1)-1)

"THEFALLGUY" wrote:

> I have a series of full names in single cells that are formatted the 
> following way:
> <Last Name>,<Space><First Name> - thus it looks like the following:
> ADAMS, ROSEMARY
> 
> I would like to separate this into two columns and eliminate the comma.  I 
> appreciate any help.  I am working in Microsoft Excel 2000 and need answers 
> in Excel, not in Visual Basic.
> 
> Thank you,
> THEFALLGUY
0
9/7/2005 12:16:57 AM
Reply:

Similar Artilces:

If column A and column B have values how do i get the total in co.
ex. A B C 1 1 1 2 2 3 1 4 3 4 1 5 4 5 1 6 It is better to express your question in the body of the message rather than the subject. However, as best I understand your question, you can use a simple formula in C1 , =A1+B1, and fill down as far in column C as you need to go. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "reness" <reness@discussions.microsoft.com> wrote in message news:3AFFE821-6E...

Totalling columns and repeating formulas in new entries
i'm working in excel 2003 i've tried searching this, but have come up with nothing. it seems s basic, i'm sure i must be calling it the wrong thing or something. in my worksheet (that's what a spreadsheet is called now, isn't it?) enter expenses in column "e" and revenues in column "i", with the ne gain or loss for that row (entry) showing up in column "j". i had n problem setting that up. the first problem i'm having is the formulas and formatting bein copied to the next entry. some of the new entries have no value i column "e&...

How to create a single line separated by commas from a matrix?
Hello, I've the following problem. I've a table made of several columns. Each colum represents a characteristic regarding to a subject (e.g. Name, Surname, Home Street, etc...). In the rows I put the different people. I need to export these info to a format compatible with other program - an email program-, which requires the information to be arranged in a different way. The program needs all these data to be ordered in a single row, with each characteristic between quotation marks, and separated by commas, following this pattern: "characteristic 1","characteristic ...

Separate inboxes for separate accounts
How can I separate incoming e-mail from separate e-mail accounts into folders so I can tell where incoming mail came from? Using Outlook 2000. >-----Original Message----- >How can I separate incoming e-mail from separate e-mail >accounts into folders so I can tell where incoming mail >came from? Using Outlook 2000. >. > I'm a novice but I just ask that question and dl@spoofmail.com gave me the following advice: It is done with rules. 1st create your folders (sounds like you've done that.) Then select Tools / rules & alerts / New Rule... This brings up...

Master image to use in separate sheets?
Hello All, Does anyone know if there is a way to tell Excel to use the same image/background for each sheet, instead of needing to import for each one? Or is there a way to set up a (similiar to Power Point/Master slide). Thanks in advance, drew If it's just for one workbook, I'd have a master worksheet in that workbook that I could just copy. If it's for lots of workbooks, I'd set up a workbook with one sheet that had that background the way I wanted it. Then save this workbook as a Template (let the folder default to excel's favorite). Tnen when you rightclick on...

color of last bar on bar chart
I have a dynamic rolling 13 month bar chart, but want to color the current month (right-most bar) a different color to the other bars. Can this be done? (needs to work as chart rolls forward each month). Thanks Hi, Simplest thing to do, without introducing VBA, is to use another dummy series which only has data for the last point. You can set the Series Option for Overlap to 100. This will place the columns/bars on top of one another so the new series will obscure the main data series. Cheers Andy PBcorn wrote: > I have a dynamic rolling 13 month bar chart, but want to color the c...

Text to Columns #10
I have a list of names that were split across 3 columns using the text to columns feature. Does anyone know of an EASY way to tell Excel to move a name into the third position if it is blank? Example: A B C 1 LastName MiddleInitial FirstName 2 Smith J Daniel 3 Taylor David 4 Pearson Tony I have thousands of records and would like to find a way to quickly move contents from the 2nd column to the 3rd IF that 3rd column is blank. Idea's anyone? Thanks! NewKid ...

how to block domain names
How do I block a domain? Outlook can't block anything. Linda Mellin wrote: > How do I block a domain? make a rule "Linda Mellin" wrote: > How do I block a domain? "Linda Mellin" <LindaMellin@discussions.microsoft.com> wrote in message news:80646AD4-196B-46FF-BAEE-CB9E57BF5953@microsoft.com... > How do I block a domain? Add the domain to the Blocked Sender list and messages from it will be placed in Junk E-mail. -- Brian Tillman [MVP-Outlook] ...

One stop shopping: Need ALL Mailbox Delegate Names & Permissions
I'm trying to programatically get all names and permission levels for users who have access to a particular mailbox. As I understand it, access to a mailbox can be granted in serveral ways. The first is via a "Send As" delegate created in Outlook (Tools->Options->Delegates). The names are stored in AD (publicDelegates attribute) and are available through LDAP queries. Problem is with the permissions, where are they stored? How can they be retrieved programatically? Another method of granting access is by adding a user to the (mailbox, inbox, calendar ...) folders in Ou...

Separating a comma separated list
Not having a great deal of experience with macros, I thought I might see if I could pick the brains of the group. I have to essentially break out a comma separated text string in a single cell into a vertical list, with a reference number thrown in for good measure. Example. Data I have: A B 1 NUMBER RESOURCE 2 101 Smith John,Jones Adam,Brown Philip,Greene Thomas 3 102 Smith John,Jackson Arthur Needs to look like: A B 1 NUMBER RESOURCE 2 101 Smith John 3 101 Jones Adam 4 101 Brown Philip 5 101 Gr...

Understanding Range Names and Labels
I have just worked through an excelent example for creating a chart directly from a formula on www.jkp-ads.com. This at last has got my head around Names (I think) eg. a name defined "sheet1!Top_left" =sheet1!$a$1 cannot be used or seen on another sheet a name defined "Top_left" =sheet1!$a$1 can be used on all sheets and always refers to sheet1 a name defined "Top_left" =indirect("a1") can be used on all sheets and refers to a1 on the same sheet as the name call. The bit I need help on (and sorry for the waffle) is what Labels are and how do I use ...

Separating strings in a field to separate fields
Hi, I have a field that consists of a string value that looks like Item1, Item2, Item3, each item is separated by commas. I want to separate that string value so that each item has its own field. Is there code that will easily do this? Thanks, Jaime On Wed, 5 Dec 2007 12:08:57 -0800 (PST), jseger22@yahoo.com wrote: >Hi, > >I have a field that consists of a string value that looks like Item1, >Item2, Item3, each item is separated by commas. I want to separate >that string value so that each item has its own field. Is there code >that will easily do this? > >Thank...

What formula do I use to show the last cell with text in a colum
I have an Excel worksheet with 2 sheets. Sheet1 is for data entry and will have data in columns to record details of meetings, each row is a different person e.g. column B has details of first meeting, column C has details of second meeting, column D has details of third meeting and so on . I want my second sheet to be a report that shows the details of the most recent meeting i.e I want a formula in sheet2 that looks at sheet1, finds the last cell in a row with text, and copies this text to the relevant cell in sheet2. Do I use "go to" command plus a formula? how? ...

Excel "chops off" half of first line of text
I have a spreadsheet where one column is cells that each contain 30 to 50 words of text. Even after applying FORMAT, ROW, AUTOFIT when I print I lose the top 50% of the characters in most, but not all, of the top line of these cells. Everything looks fine on screen prior to printing. Has anyone any ideas or work-rounds, please? Hi, The easiest (and non programmatic) way to do this is to highlight the sheet, and mouse over one of the row divisions (i.e. between 2 rows, which you would normally use to resize). Double clicking here will expand all rows to autofit contents. Otherwise,...

Exchange 2003
Hello All, Yesterday night we started receiving lots of errors on our Exchange 2003 SP1 server (Exchange is in native mode). Server reports that there are constant attempts to create named properties, so that the quota (36K) had been exceeded. Here's the typical error message: 1) Event ID 9667, source: MSExchangeIS, Category: General Failed to create a new named property for database "First Storage Group\Mailbox Store (MVEXCH1)" because the number of named properties reached the quota limit (16384). User attempting to create the named property: "BESAdmin" Named prope...

Launch Oulook Express Newsreader separately
Hi I use Outlook Express as my newsreader. My problem is that in order to access it, I need to first launch Outlook. Is there some way I can launch the newsreader portion of OE without launching Outlook? A command line switch or something I imagine. I'm using Outlook XP with OE 6 on Windows XP pro. Thanks I believe the command-line switch is /newsonly. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "aJax&...

Name Badges in Publisher?
I am trying to create custom Name tags in publisher. I want to print 8 different names on one sheet. Right now, I can only get one name per sheet on 8 tags. How do I do this. Publisher 2003 (your didn't say) help returned this: If you have a package of Avery labels with a specific product number, you can quickly open a matching label publication by using the Page Setup dialog box. 1.. On the File menu, click New to open a new publication. 2.. On the File menu, click Page Setup. 3.. Under Publication Type, select Label. 4.. Under Page Size, click the scroll arrows at the top...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

SHEET NAMING #2
HOW DO I NAME VARIOUS SHEETS IN MY WORK BOOK WITH DATES OF A MONTH e.g. FEB 9, FEB 10 ETC IN ONE SHORT try this in Sheet1 , Range A1: A10 type FEB 9, FEB 10 and so on run this macro Sub sheet_name() For a =3D 1 To Sheets.Count Sheets(a).Name =3D Cells(a, 1).Value Next End Sub On Feb 9, 12:56=A0pm, rnks...@gmail.com wrote: > HOW DO I NAME VARIOUS SHEETS IN MY WORK BOOK WITH DATES OF A MONTH > e.g. FEB 9, FEB 10 ETC > IN ONE SHORT Double-click the sheet tab and over-type the value. -- __________________________________ HTH Bob <rnkshan@gmail.com> wrote in message new...

Column Heading Query
I am using Outlook 2003. I have created an extra folder to move sent items into. There is no 'To' column displayed. I have been able to create a 'To' column. But, the 'To' address of each entry is not displayed in this column. How do I achieve this objective? TIA "KiwiBrian" <briantoz@ihug.co.nz> wrote in message news:h9bkks$kfh$1@lust.ihug.co.nz... >I am using Outlook 2003. > I have created an extra folder to move sent items into. > There is no 'To' column displayed. > I have been able to create a 'To' column. > But,...

When was a DL last used? #2
I am trying to script or develop some sort of way to see which DLs are not being used. I know promodag has something to this effect but I am at a new job that apparently does not have message tracking turned on. Can this be retrieved by date or other means? ...

separate pages rather than spreads?
Hi Using 2007 I created a .pdf of an A5 booklet with 80 double sided pages and sent it to an outside printer. They replied asking "Would it be possible to send in the inside pages as separate pages rather than spreads? That is a pdf with the pages A5 size in number order 1- whatever." Anyone know how to do that please ?? -- Martin ���� Martin ���� @nohere.net wrote: > Hi > Using 2007 > I created a .pdf of an A5 booklet with 80 double sided pages and sent > it to an outside printer. They replied asking > > "Would it be possible to se...

A document with the name "xx" is already open......
Opening excel documents directly from the My Documents folder with the program not open we get the following error message: "A document with the name "xxx.xls" is already open. You cannot open ttwo documents with the same name, even if the documents are in different folders. To open the second document, either close the document that's currently open or rename one of the documents" No other documents are open. When we close the error message everything is fine. When we first open Excel and then open from there, we do not get this error message. How can we...

Need to Separate
Hello All, I was wondering if there is a way to separate the following: I have the following: TX;CA;NM;VT;NY;MI;OK;FL I need it to be: TX CA NM VT NY MI OK FL Thanks, Charles Data>Text to Columns>Delimited by ; will get it into columns. Then copy>paste special>transpose will get it into one column. Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 21:02:46 -0500, "Charles Reid" <creid@satx.rr.com> wrote: >Hello All, > >I was wondering if there is a way to separate the following: > >I have the following: > >TX;CA;NM;VT;NY;MI;OK;FL &g...

How to split cell based on capitalised suburb name
Hi I have data such as the following in a single cell per line Level 8, 160 Marsden St, PARRAMATTA 15 Carter Street, HOMEBUSH BAY 223 - 239 Liverpool Road, ASHFIELD I want to put the Suburb name into a seperate cell without going through hundreds of rows of data manually. There can be spaces in the suburb name and multiple commas in the field. The only way to differentiate the suburb name is that it is in UPPERCASE. Does anyone know a formula or macro that could complete this? Thanks Check out whether the below would help. The below will extract the information af...