How do I convert last name, first name in cells to first name las.

Cells contain names in last name, first name order.  I wish to convert to 
first name last name (no comma) without retyping.  Thanks
0
southern (1)
2/22/2005 7:53:06 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
728 Views

Similar Articles

[PageSpeed] 10

Assuming your data is in column A.  Select the names in Column A.  Make sure 
that Columns B and C are blank.  In Excel 2003, click on data, 
text-to-columns and choose delimited and comma.  The last name of the first 
entry will be in A2 and the first name will be in B2.  In C2 type =B2&" "&A2 
to concatenate the name with first name first and then copy down the formula 
in C2 as far as necessary.  As a last  step be sure to copy Column C, go to 
Edit - paste special -values and paste back on Column C.

"southern" wrote:

> Cells contain names in last name, first name order.  I wish to convert to 
> first name last name (no comma) without retyping.  Thanks
0
Michael5492 (655)
2/22/2005 8:53:06 PM
=TRIM(MID(A1,FIND(",",A1,1)+2,256)&" "&LEFT(A1,FIND
(",",A1,1)-1))
>-----Original Message-----
>Cells contain names in last name, first name order.  I 
wish to convert to 
>first name last name (no comma) without retyping.  Thanks
>.
>
0
hightide (27)
2/22/2005 9:22:21 PM
FWIW- Michael's reply should give you what you want if the data is 
consistant, but I would offer another thought when it comes to the "In C2 
type..." step on - leave it out. Store the first & last names in separate 
columns in order to more easily produce true alpha sorts and filters, mail 
merge, etc.  based on the content of either field. You can always use his 
concatenation formula to combine the content of the two fields for display 
purposes, but it is very limiting to store it that way.

Even if your current needs don't require it, sooner or later it will pay 
off. Make sense? |:>)

"southern" wrote:

> Cells contain names in last name, first name order.  I wish to convert to 
> first name last name (no comma) without retyping.  Thanks
0
CyberTaz (411)
2/22/2005 9:45:01 PM
=?Utf-8?B?c291dGhlcm4=?= wrote

> Cells contain names in last name, first name order.  I wish to convert
> to first name last name (no comma) without retyping.  Thanks

One more way:
=IF(A1="","",RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(",",A1)-1))

-- 
David
0
2/22/2005 10:09:03 PM
Reply:

Similar Artilces:

Lookup and Sum in same cell
SHNAME ENTDATE SYMBOL QTY RATE COMM C78 1/24/2005 CSCO 1100 0.018 $20 C78 1/28/2005 CSCO -5439 0.018 $100 C78 2/23/2005 CSCO 50 0.017 $1 C78 4/5/2005 CSCO -7892 0.018 $140 C78 4/13/2005 CSCO 3786 0.018 $69 I am trying to create a lookup formula that will search by SHNAME, find each entry and sum the COMM column in one cell: SHNAME COMM C78 $360 This worksheet is thousands of rows long with multiple rows for each SHNAME. Thanks in advance -- Potatosalad2 ------------------------------------------------------------------------ Potatosalad...

Cell Border Line Width
Is it possible to specify the cell border line width using Excel 2002? The "Border" tab in the "Format Cells" dialog box allows me to choose predefined line widths but I need a width other than what is offered. Is there a way to do this? Thanks! Adam Not with the borders property...you are restricted to xlHairline, xlThin, xlMedium, xlThick"....... You could *possibly* fake it with the AddLines method of the worksheet but I wouldn't really want to try.....;o) OJ ...

First post, need help guys!
Hi to all, I pasted together the below attachment. Can anyone help me with it. I need to know how to start a second grap 55% of the way up the original graphs axis. http://i10.photobucket.com/albums/a121/Moxy1980/graph.jpg PS. I cant get the pic bigger but can mail if you leave a reply on th thread. Thanks again -- Moxy198 ----------------------------------------------------------------------- Moxy1980's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2807 View this thread: http://www.excelforum.com/showthread.php?threadid=47583 Moxy The second set of dat...

Locked cells in IE
Hi, I need to open an Excel document from a web page. Active X controls are restricted and I cant pass the document as a parameter in a shortcut to the Excel executable because it needs to be generic (and would not e in this case). The problem Im having is that the Excel document contains locked cells that need to remain locked. When the document opens within the internet explorer interface the locked cells are not visible and the spreadsheet looks a bit disjointed. Does anyone know another way to open the document from a web page or how to make the cells visible? Many many thanks...

UPPER CASE formatting a cell
I receive excel data from a number of sources to be combined together in a single sheet. I would like to format a column of cells to be all upper case but not use the =UPPER() function. I'd like to format a cell to force any lower case char to upper case automatically when data is entered. Custom formatting as >CCCC doesn't work. Can this be done? Conversely, how can I force cell A1 to be UPPER CASE by using =UPPER(A2), then remove the reference to A2 when printing the result? I don't want the cell A2 to print. This I forget how to do. Hi you'll need VBA for this. ...

Excel 2007 convert into tab delimeter text file with extra tab spa
Hi , Sub: I have excel 2007 trying to save as tab delimeted text file having an issue. I have saved my excel 2007 as separate Tab delimited Text files. I see extra tabs stored in the tab delimited text files. But when I do the same with excel 2003 it works fine there is not extra tabs stored in my tab delimited text file. My excel 2007 conveted to tab delimited text file looks like as shown below(Extra two tabs at the end of the each record). Data0<Tab>data1<Tab><Tab> Data2<Tab>data3<Tab><Tab> Appreciate your help in advance. Rega...

find last cell in column
Hi, Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be ..388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 ...

Tweaking cells
If I have cells that may remain blank, how do I get them to be blank instead of showing a 0? These cells are set for text, not numbers, so I don't understand why a number would show there by default. TIA Steven Connor Hi do you have formulas in these cells and if yes what kind of formulas? -- Regards Frank Kabel Frankfurt, Germany **{Steven}** wrote: > If I have cells that may remain blank, how do I get them to be blank > instead of showing a 0? These cells are set for text, not numbers, so > I don't understand why a number would show there by default. > > TIA >...

Critique a function to convert time, please
I have a bunch of data representing time intervals that come from a database application. The intervals vary from a few seconds to several years. The database (MySQL) stores time intervals as fixed point numbers in units of seconds. Reading the values in seconds is unnatural (a year is ~31.5E6 seconds), so I wrote a little UDF to convert the seconds to more natural units. The conversion is: Value Units <60 seconds Seconds <60 minutes Minutes <24 hours Hours <99 days Days Otherwise Years I chose 99 days as the threshold betwe...

Text cell complains about date
I have a column containing ratios like 3/4, 3/0, 5/2, etc. I have them all formatted as text. All of the cells that have a zero as the second number (3/0) get a little green triangle and a warning that I have entered a "date string with only 2 digits for the year". Huh? How is "3/0" a date string with 2 digits for the year? And, why is it complaining about a text field? More importantly, how do I get Excel to stop being so "helpful" and leave my damned text data alone -- unexamined? Remove the warning in Options, Tools>Options>Error Checking in 2003...

Extract First Name From String
How can I extract the First Name only from the String Below? I only want to pick up 'Veronica' I can get the Last Name by using CHARINDEX(',',[Patient Name])-1 [Patient Name] = LUNA,VERONICA E -- Brian Conner Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1 Brian SELECT LEFT(@Value, CHARINDEX(',', @Value)-1), "Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message news:a0558fa5b6081@uwe... > How can I extract the First Name only from the String Be...

in Excel 2000 how do I convert text to numbers?
I imported files from Wells Fargo and I want to convert them from text to numbers...so far nothing is working! thanks Jo Dee One method...... Format all as General. Copy an empty cell(also formatted as General). Select range of numbers. Edit>Paste Special>Add>OK>Esc. One other method......... Select range of numbers and Data>Text to Columns>Finish Gord Dibben Excel MVP On Sun, 27 Feb 2005 11:53:06 -0800, Jo Dee <Jo Dee@discussions.microsoft.com> wrote: >I imported files from Wells Fargo and I want to convert them from text to >numbers...so far nothi...

Cursor jump to first field
Hi! In A2007 I have a form with TabCtl with pages. In each page I have one subform with field and comboboxes. After choose an option in theese combos show us textboxes. If to accede the field I need to use the scrollbar, when write in textbox the cursor jump to yhe 1st field in this sub form. Consequently we need to use the scrollbar again to to go next field and like this successively. How is possible the cursor to stay inside of the last field and to avoid to jump and to turn to scrollbar? Thanks in advance. an ...

Change the name of an auto save
I am trying to write a macro that will save the file to a specific cell reference. The reference is a specific name that is automatically generated by another macro function. Any suggestions on how I could do this? Cheers IS thsi what you want? Sub SaveMe() sFilename = Format(Worksheets("Sheet1").Range("A1").Value) ans = MsgBox("Save file as " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs Filename:=sFilename End If End Sub HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Y...

Apply a mutiple to a range of cells?
I have a range of cells spanning multiple columns and rows. I would like, in my fantasy, to just hightlight all of them and say "multiply by x" where x is an integer. Now, I know I can't do that because my microphone doesn't work, so, other then a macro, is there a way to do it? Thanks, -Jim In an unused cell enter the numnber that you want to multiply by - it can be an integer or a floating point number - then highlight the range to you want to mutiply and then right-click and select Paste Special > Multiply > Ok Don't forget to say "Multipy by x"...

Delete Name Box!
I know how to create a Name Box for a range, but how do you delete it?! It is like I forgot it is done! Insert - Name - Define. Select Range, Delete. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mike" wrote: > I know how to create a Name Box for a range, but how do you delete > it?! It is like I forgot it is done! > . > ...

change cells contents from vertical to horizontal
I need to copy from one workseet the values in cells that are arranged vertically into another worksheet and the values need to be arranged horizontally. Can this be done? If so, how? Select the data to be copied>Copy>Select target cell>Right click>PasteSpecial>select 'Transpose>OK If this post helps click Yes --------------- Jacob Skaria "GailH" wrote: > I need to copy from one workseet the values in cells that are arranged > vertically into another worksheet and the values need to be arranged > horizontally. Can this be done? ...

Convert Excel records to text documents in arbitary formats
Hi, A newbie here (groan). I need a tool to take data in excel tables and generate text documents with the data rearranged in new forms, eg to look somewhat like mailing labels (but with more data items, like email address). Is there a tool that will take Excel data and generate documents in arbitrary formats? Also, is there a tool to edit column data, eg. the Name field I have is all uppercase, and I wanted to cap the first and make the others lower case (eg: JONES -> Jones), a pain in the ass if done manually. Generally, an editing tool for column data. thanks in advance Don't kno...

Chinese name sorting in Outlook
Would anyone familiar with Chinese tell me how I might be able to sort my address book correctly. Currently, it's a mess and abc with the chinese names creating little to no order. My old p[alm pilot and palm desktop sorted it properly with english first and then chinese based on characters and the pinyin system, but now that I converted it to Outlook- everything is a mess. Any help would be much appreciated. Thank you ...

Message box warns for duplicate use of named ranges when copying s
I created an Exel/VBA2003 application in which a copy of a worksheet is added when the user pushes some button on the main form. In the worksheet which is copied, I use a few named ranges. This works fine in 2003. In 2007, however, a message box pops up that warns for duplicate use of the named range in the new sheet. Since there are 5 ranges, it is quite annoying to cancel these message boxes every time. Can I programmatically prevent these msg boxes from popping up ? Thanks ! Found one answer on pcreview (gracias a Hector Miguel): add Application.DisplayAlerts = False a...

Chart name
Here is an easy one, I guess, for those who know the answer: Charts on a worksheet are named Chart1, Chart2, etc. as they ar created, and this is the name you must refer to when manupulating the in VBA. Is it possible to look up the name of a chart, and if affirmative is i possible to change it? NS -- ns ----------------------------------------------------------------------- nsv's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2650 View this thread: http://www.excelforum.com/showthread.php?threadid=55725 Hi, Hold the Shift key whilst selecting the chartobje...

Creating new series requires div cell val by itself . How to do t.
I am trying to rebase a time series which requires dividing the first value in each series by itself. I keep getting circular error messages regardless of how I format the calculation. Is ther a way around this issue? Texas When you divide a number(and time is a number) by itself you will get 1. Is that what you want? You could use a couple of helper columns. Assume data in column A. In B1 enter =A1. In C1 enter =A1/B1. Gord Dibben Excel MVP On Mon, 21 Mar 2005 12:35:08 -0800, "Texastom" <Texastom@discussions.microsoft.com> wrote: >I am trying to rebase a time ...

convert text to number format
Hello, I have a column of numbers that have the comment "The number in this cell is formatted as text or preceded by an apostrophe". I'd like to convert all these these values to numbers so I can get a summation. By mousing over the cell I can get a menu that allows me to convert the numbers one at a time, but how can I convert them all at once? I've tried converting with the Format/Cells and Edit/Copy/PasteSpecial/values. Thanks in advance, Ellen I found the answer: In an empty cell, enter the number 1. Select the cell, and on the Edit menu, click Copy. Select the...

Speed Search in pick list by typing only first few letters
Is there any facility to pick from the list by typing only first few words (2 or 3) and the curser goes to the matched or nearby entry in the list arranged alphabetically ? This is similar to locating our topic in the Help index. In the pick list feature, I have to manually move the curser to the desire entry. If the list is long, it is not practicable to scroll down the list. -- Shrikant A combobox from the control toolbox toolbar has this ability. Shrikant wrote: > > Is there any facility to pick from the list by typing only first few words (2 > or 3) and the curser goes to ...

sum cells
I am trying to total employee schedule so some cell have D/O day off, R/O requested off, so when I try to total all associates by the day I get #VALUE! in cell and total cell. Is there a way to total only numbers and ignore #VALUE! Try this: =SUMIF(A1:A10,"<>#VALUE!") Why not fix the formula that causes the error in the first place? What is the formula that does this: >when I try to total all associates by the day I get #VALUE! Biff "SteveD" <SteveD@discussions.microsoft.com> wrote in message news:7D8BF85A-F76E-4F35-95AC-50B77B128304@microsoft.com....