converting cell data / opposite?

Hello Excel gurus...

I have a cell/column containing a persons name:
John Doe

I'd like to convert cell/column to read:
Doe, John

What formula is used in order to make this happen?
I've tried left/right/len commands out the wazzoo (technical word) - I
give... uncle....

Any help?
Jorge


0
czar1259 (15)
9/9/2004 2:20:28 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1051 Views

Similar Articles

[PageSpeed] 30

Hi Jorge

in another cell use
=RIGHT(C14,FIND(" ",C14)-1) &", " & LEFT(C14,FIND(" ",C14))

where C14 has the original data in it

Cheers
JulieD

"Jorge" <czar1259@netscape.net> wrote in message
news:10k0ptons6i6e4c@corp.supernews.com...
> Hello Excel gurus...
>
> I have a cell/column containing a persons name:
> John Doe
>
> I'd like to convert cell/column to read:
> Doe, John
>
> What formula is used in order to make this happen?
> I've tried left/right/len commands out the wazzoo (technical word) - I
> give... uncle....
>
> Any help?
> Jorge
>
>


0
JulieD1 (2295)
9/9/2004 2:25:05 PM
Julie (thank you)

Works okay except for some reason longer names it cutting off first few
xters?

ie
Walter Robinson
returns result
binson, Walter

Any clue?
Jorge

"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:#vFgslnlEHA.2820@TK2MSFTNGP15.phx.gbl...
> Hi Jorge
>
> in another cell use
> =RIGHT(C14,FIND(" ",C14)-1) &", " & LEFT(C14,FIND(" ",C14))
>
> where C14 has the original data in it
>
> Cheers
> JulieD
>
> "Jorge" <czar1259@netscape.net> wrote in message
> news:10k0ptons6i6e4c@corp.supernews.com...
> > Hello Excel gurus...
> >
> > I have a cell/column containing a persons name:
> > John Doe
> >
> > I'd like to convert cell/column to read:
> > Doe, John
> >
> > What formula is used in order to make this happen?
> > I've tried left/right/len commands out the wazzoo (technical word) - I
> > give... uncle....
> >
> > Any help?
> > Jorge
> >
> >
>
>


0
czar1259 (15)
9/9/2004 3:24:25 PM
Hi Jorge

sorry it's

=RIGHT(C14,LEN(C14)-FIND(" ",C14)) &", " & LEFT(C14,FIND(" ",C14))

(second time today i've made the same mistake!)

Cheers
JulieD


"Jorge" <czar1259@netscape.net> wrote in message
news:10k0tlldpb0qk3f@corp.supernews.com...
> Julie (thank you)
>
> Works okay except for some reason longer names it cutting off first few
> xters?
>
> ie
> Walter Robinson
> returns result
> binson, Walter
>
> Any clue?
> Jorge
>
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> news:#vFgslnlEHA.2820@TK2MSFTNGP15.phx.gbl...
> > Hi Jorge
> >
> > in another cell use
> > =RIGHT(C14,FIND(" ",C14)-1) &", " & LEFT(C14,FIND(" ",C14))
> >
> > where C14 has the original data in it
> >
> > Cheers
> > JulieD
> >
> > "Jorge" <czar1259@netscape.net> wrote in message
> > news:10k0ptons6i6e4c@corp.supernews.com...
> > > Hello Excel gurus...
> > >
> > > I have a cell/column containing a persons name:
> > > John Doe
> > >
> > > I'd like to convert cell/column to read:
> > > Doe, John
> > >
> > > What formula is used in order to make this happen?
> > > I've tried left/right/len commands out the wazzoo (technical word) - I
> > > give... uncle....
> > >
> > > Any help?
> > > Jorge
> > >
> > >
> >
> >
>
>


0
JulieD1 (2295)
9/9/2004 3:28:25 PM
Thanx Julie...
I saw the LEN out was the issue...

Have a good dy!

Jorge

"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:#jSSLJolEHA.2340@TK2MSFTNGP11.phx.gbl...
> Hi Jorge
>
> sorry it's
>
> =RIGHT(C14,LEN(C14)-FIND(" ",C14)) &", " & LEFT(C14,FIND(" ",C14))
>
> (second time today i've made the same mistake!)
>
> Cheers
> JulieD
>
>
> "Jorge" <czar1259@netscape.net> wrote in message
> news:10k0tlldpb0qk3f@corp.supernews.com...
> > Julie (thank you)
> >
> > Works okay except for some reason longer names it cutting off first few
> > xters?
> >
> > ie
> > Walter Robinson
> > returns result
> > binson, Walter
> >
> > Any clue?
> > Jorge
> >
> > "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> > news:#vFgslnlEHA.2820@TK2MSFTNGP15.phx.gbl...
> > > Hi Jorge
> > >
> > > in another cell use
> > > =RIGHT(C14,FIND(" ",C14)-1) &", " & LEFT(C14,FIND(" ",C14))
> > >
> > > where C14 has the original data in it
> > >
> > > Cheers
> > > JulieD
> > >
> > > "Jorge" <czar1259@netscape.net> wrote in message
> > > news:10k0ptons6i6e4c@corp.supernews.com...
> > > > Hello Excel gurus...
> > > >
> > > > I have a cell/column containing a persons name:
> > > > John Doe
> > > >
> > > > I'd like to convert cell/column to read:
> > > > Doe, John
> > > >
> > > > What formula is used in order to make this happen?
> > > > I've tried left/right/len commands out the wazzoo (technical word) -
I
> > > > give... uncle....
> > > >
> > > > Any help?
> > > > Jorge
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
czar1259 (15)
9/9/2004 3:52:14 PM
Jorge,

You may find you'll need the first and last names separated into separate
columns, such as to do a sort by last name.  You might want to consider
converting your table.  Here are two formulas, just parts of Julie's

Last Name:   =RIGHT(C14,LEN(C14)-FIND(" ",C14))
First Name:  =LEFT(C14,FIND(" ",C14))

You could put these in a couple of columns (copied down with the fill handle
as necessary), then you could make it permananent, allowing you to remove
the original column with the full names.  Select the columns, Copy, then
with them still selected, Edit - Paste Special - Values.  Now remove the
original full name column.

Where needed, you could combine them into full names.  If the last name in
C15, and the First name in C16, you could use

Doe, John:     =C15 & ", " & C16
John Doe:      =C16 & " " & C15
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net

President Bush on sovereignty:
http://www.tastefullystated.com/sovereignty.wmv

-------------------------------------------

"Jorge" <czar1259@netscape.net> wrote in message
news:10k0ptons6i6e4c@corp.supernews.com...
> Hello Excel gurus...
>
> I have a cell/column containing a persons name:
> John Doe
>
> I'd like to convert cell/column to read:
> Doe, John
>
> What formula is used in order to make this happen?
> I've tried left/right/len commands out the wazzoo (technical word) - I
> give... uncle....
>
> Any help?
> Jorge
>
>


0
nothanks4548 (968)
9/10/2004 2:38:13 PM
Reply:

Similar Artilces:

Need help converting "InstalledOn" date for all Win32_QuickFixEngineering entries
Hello, all. I'm new to Powershell scripting, but have used VBScript for years. I've found that there is no way in VBScript to do the following and am hoping someone can help with a PowerShell script for the following: I need to be able to document all of the HotFixIDs, their description, and the date that the HotFixes were installed. I want this list to be a CSV list available in the root directory of the computers that need this information. All of the computers that I'm going to use this on are 64-bit computers and are running Windows Server 2008 and Vista. I found ...

Video Converter for ipad
&nbsp;How to Convert 2010 World Cup FIFA Camera Videos to ipad video format?The South Africa World Cup FIFA began from June 11, 2010. ~~~~(&gt;_&lt;)~~~~ If you want to review the favourite games on your ipad, you can check this out--Gskstudio iPad Video Converter. It's really helpful. O(?_?)O~&nbsp;---You can use Gkstudio iPad Video Converter to convert any format of the video (even high definition video) into iPad video format.---You can use Gkstudio iPad Video Converter to extract iPad audio from video files and convert audio formats to iPad audio as it?s well establishe...

How can I load data from existing accounting systems?
My client has an existing huge working database (Quickbooks Pro 2003) that he wants to import into RMS. Is this possible, or do we need to load all existing data with manual data entry? Nope just set up intergration and under utitilys import any data you need. I did a lumber yard that way and it works great. "Andrea Pope" <Andrea Pope@discussions.microsoft.com> wrote in message news:025C2302-0AB0-4530-B201-1F01553F5E58@microsoft.com... > My client has an existing huge working database (Quickbooks Pro 2003) that > he > wants to import into RMS. Is this possible...

stop rounding in cell
i want the numbers in my cells to show as they are without being rounded but to show in thousands. I want to maintain integrity. Eg. 97,654 should just show as 97. i don't want it to round up or down. is this possible? Hi Try =INT(A1/1000) but note anything less than 1000 will show as 0. Regards Roger Govier Lady112017 wrote: > i want the numbers in my cells to show as they are without being rounded but > to show in thousands. I want to maintain integrity. Eg. 97,654 should just > show as 97. i don't want it to round up or down. is this possible? Try this formula: ...

Import data from FileMaker
I have a School Suspension file that is in FilemakerPro. It works very well. I want to automate a monthly report for our business office and want to be able to import data from the db into Excel. On Mac's I can do so quite nicely, simply Open Excel, then Open the FMPro db which launches a wizard and all is done in a few key strokes. Obviously I'm not as adept in XP. Using Excel 2003 and FileMaker Pro 6.ov4 how can I accomplish this task? I don't speak FileMakerPro, so you can try this or wait for a real response! Open excel. File|Open Look at all the fileformats that excel ...

Query data and formatting
I have a query on a VFP table (linked through ODBC to our sales software) to summarize the daily value of sales orders entered. I need to group it into weekly amounts and daily amounts but cannot manipulate the data in the query to finish it in a report. I have the daily values totalled by day, but cannot group it beyond that. My end result should display: Date Daily Amount MTD Amt YTD Amt Week of (date) with sub-totals Is there a query criteria formula I can use so I can keep the link to the live data? ...

Advanced Filter sort on "asterisk" (wild card) hides data, not just empty blank cells.
In debugging a program, I isolated the problem to the following issue. I manually created the following situation in column A, rows 1 to 6, by copying and pasting actual data in order to accurately re-create the problem: ABN/ACN/BN * ABN/ACN/BN 3158816 40000545415/005 Excel "Help" has this to say about the asterisk: " * (asterisk) Any number of characters in the same position as the asterisk For example, *east finds "Northeast" and "Southeast" " I am assuming that the asterisk, being the wild card symbol, being the "cell not empty" symbol, ...

Hiding cells based on the value in another cell
Hi, I am using Excel 2007. I have created a worksheet where I would like to be able to "hide" a group of cells based on the value of another cell. I can do what I want by inserting a shape (rectangle) and giving it a white background and a white line color. As I have chosen not to show gridlines on my worksheet, this rectangle effectively hides the cells that are behind it. I can write code to control the visibility of this rectangle based on the value in a particular cell. I imagine that there is a way I can get this code to run anytime the value in this cell...

Cell Error Message
I am running Excel 2002 and keep getting a comment like deal in all cells that are not calculating to the end of the range. For example if I have data in Col. a.b.c.d.e.f but do not include Col. a, I get a comment type message that the formula did not calculate the entire range(which I do not want it to). I know that I can turn this off manually (in each and every cell effected)(about 50), however does anyone have an idea of how to turn it off completely, either my use of a VBA or Excel itself. Thanks Frank ...

Keeping the Same Cell References
What's the easiest way of copying a cell with a formula in it and pasting it into another cell with the exact same formula? I also have ASAP utlities, and I couldn't figure out how to do it with that either. One way: Remove the = sign before copying and re-insert it in the new location after pasting -- Kind regards, Niek Otten Microsoft MVP - Excel "JP" <JohnP26@msn.com> wrote in message news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com... | What's the easiest way of copying a cell with a formula in it and | pasting it into another cell with the exact same for...

Lock cell for number input
Hi, I am creating Excel file for users to fill in the data and send it back to me. I have cells that require users to put in numeric value. How can I do to only allow numerice value when users typing in the cells? I need to do the same for percentage as well. For the percentage, my idea is to set the format of the cell to be "Percentage" and then limit the value to between 0 to 1. Thank you very much, B On Jun 23, 7:48=A0am, "msnews.microsoft.com" <boonyawat.la- ongth...@cnh.com> wrote: > Hi, > > I am creating Excel file for users to fill in the da...

Can I highlight a cell and link it to another workbook?
I need to highlight one cell and link it to another cell in a different workbook where I want to enter a text definition of the first cell. I don't think linking works because that just gives me the same information in a different cell. I want to be able to click on a cell in one worksheet and be able to "flip" to its definition in another worksheet. Thanks! -- Jenn Create a hyperlink... Click the cell you want to link so it is the active cell. Go to the Insert menu and choose 'hyperlink' In the hyperlink window select the 'Place in this Document' button...

Re: Find last cell in a column, Delete its contents and make it ac
Sub uselastcellinL() Application.Goto Cells(Rows.Count, "L").End(xlUp) ActiveCell.ClearContents End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Don Guillett" <dguillett1@gmail.com> wrote in message news:... > Sub deletelastcellinL() > Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp > End Sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "George" <George@discussions.microsoft.com> wrote in message > news:D...

Data Validation mystery
Using Excel2003 with Windows xp One Workbook - Two worksheets Two columns in worksheet1 - Data Validation in cells of both columns Selecting a word from the drop down list in column 1 allows selection of a word from a drop down list in column 2 (changing the word in column 1 offers a different drop down list of words in column 2). Worksheet2 has 4 columns of words, each column with a Heading Column 1 has its own Heading and contains the Heading of columns 2, 3 & 4 Columns 2, 3 & 4 each contain a different list of words Named ranges: Each column Heading is 'Named' using the...

Ooh .. Linking a list to a list to an output cell
I have a single cell in which i enter the price of a home. Elsewhere, I have two columns of ten items each: one column is formulated to give relative prices based on the price entered in the single cell, and the column adjacent to this column describes these numbers in nominal terms (i.e. ten items in increments of five percent: "25% more" .. " 0% change" .. "25% less"). Further along, I have a list box created which uses the nominal column at the Source for the list. I would like to have a cell adjacent to the list box cell which will give the price b...

how do I concatenate color attributes with data
hello excel experts, I have a frustrating problem...I would like to find out if there is way to attach the format of the data within cells when using th concatenate function. For example: if cell A1 had a bold 3 in it, an A2 had a regular 3 in it, then the result of the concatenation functio of those two cells would be a cell with one bold 3 and a regular 3 i it. Does that make sense??? Help Please!! -- Message posted from http://www.ExcelForum.com Hi not possible with formulas. Formulas can only return values but not formats -- Regards Frank Kabel Frankfurt, Germany > hello exc...

BCM release version, Migrationof old data
Is there a workaround or fix to the problem that BCM does not export its History or Opporunities dta and nor does the release version read the backup from the Beta? Since the beta is due to expire at month end, users face the prospect of data locked in teh old inaccessibile system with no way to migrate it to the current relese system. ...

Active X and Data Grid
I am trying to access a SQL database and get a list for the user to select from to be used in additional queries. I have created an ActiveX Data Object and a Data Grid Object, and I am trying to figure out how to link the two. In visual basic, in the properties, there is a line called datasource that links the two that isn't present in VBA. Any help would be appreciated. "Richard Mogy" wrote ... > I am trying to access a SQL database and get a list for the user to select > from to be used in additional queries. > > I have created an ActiveX Data Object and a D...

Locking cells #7
Is it possible to lock the cells with conditio. I want to lock all the cells after one week from the certain date on the sheet. for Ex. $A$23 cell have the date 15 Aug 2005 and i want to lock the cell $C$8 to $H$23 after one week from that date. so that no one can change the data. Thanks in advance By default all cells are locked. You would need to unlock all desired and then have a macro to lock the desired cells. Sub lockcells() Range("b1:d12").Locked = False If Date > Range("a4") + 15 Then Range("b1:b4").Locked = True End Sub -- Don Guillett SalesA...

Keydown Event while in a data Entry Field
I have a Keydown event checking for F10 on a data entry form. In my form I have 10 data entry fields. Let say I put data in the first 5 and after typing in the 6th (without moving out of the field) I hit the F10 key. The F10 logic processing only recognizes the values in fields 1-5 and nothing in field 6. I there a way to commit the values that were started to be typed in Field 6 in the start of the logic of the F10 keydown event. Thanks, Don OK I figured out a work around, but I would still like to hear of other ways of accomplishing the same thing. my workaround: Af...

Convert Access 2003 Back To Access 97 Problem
When I use the Access 2003 utility to convert my database back to Access 97, all the table links (ODBC to SQL Server) turn into local Access tables. Has anyone run into this problem and what can be done about it, if anything? Delete the tables and create new links. The work of minutes: file, import, as links. The wizard is too dumb to recognise ODBC links. It assumes all links are to Jet 4 tables, which won't be recognised by A97. (david) "MikeW" <mwhitson@msn.com> wrote in message news:1133954804.989951.93560@g44g2000cwa.googlegroups.com... > When I use the Acces...

control chart scale with cell value in excel
I would like to control the Min. and Max. values for the x & y axes in a XY scatter chart with cell values. Does anybody know how to do this? Excel 2003 jjw: Here's a post that shows a simple[le VBA approach. http://processtrends.com/pg_charts_set_x_axis_min__max_dates.htm Kelly O'Day http://processtrends.com "jjw" <jjw@discussions.microsoft.com> wrote in message news:B743C713-7053-42F4-B005-2C7B594AA86F@microsoft.com... >I would like to control the Min. and Max. values for the x & y axes in a XY > scatter chart with cell values. > Does anybody...

Conditional Formatting on multiple cells
Can a conditional format be applied, ie display a colour, to a range of cells based on a conditon in one of the cells contained in the range? Hi Karyn, If everything is dependent on a single cell, or if everything on a row is dependent on a cell in the row there is no problem. Past that how about an example. See : http://www.mvps.org/dmcritchie/excel/condfmt.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Kar...

Formatting number cells
I want to show a zero as a dash and negative numbers as (#). I can do one or the other but how can I do both? Is there any reference which explains the format specification in the custom list of options.? On 29 Jun 2005 08:00:48 -0700, "geoff" <geoffsullivan@freeuk.com> wrote: >I want to show a zero as a dash and negative numbers as (#). I can do >one or the other but how can I do both? > Format/Cells/Number Custom Type: #;"(#)";- >Is there any reference which explains the format specification in the >custom list of options.? type "Number ...

exclude cells with certain criteria
I have a large dataset (over 20,000 rows) with monthly performance of certain companies. column A is the date (A1-A12 january-december for company x; A13-A24 january-december for company y etc.) Column B is the company name(B1-b12 company name x; B13-B24 company name y etc.) Column C is the monthly performance. e.g. c1 is performance of January of company x C2 is performance of February of company x, c13 is performance of January of company y and so on Some companies only reported performance for the months (e.g.) March till december. I want to exclude those companies that didn't r...