how do I reveerse name order last name, first name to first name, last name

I have a long database, 27k with names that I want to mail to.  The
list is last name, first name.  I want to do a global change to first
name, last name.  I also want to add "Mr" to it.  For example:  the
list now is Smith, John.  I want to change it to Mr. John Smith for
the entire list.
0
dhsmith (66)
11/29/2006 3:28:06 PM
excel 39879 articles. 2 followers. Follow

9 Replies
580 Views

Similar Articles

[PageSpeed] 2

Assuming there is only one comma in each:

="Mr. "&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))&"
"&LEFT(A1,FIND(",",A1)-1)

Note that some of the women might be offended.... 27k names, all male?

Scott

Don Smith wrote:
> I have a long database, 27k with names that I want to mail to.  The
> list is last name, first name.  I want to do a global change to first
> name, last name.  I also want to add "Mr" to it.  For example:  the
> list now is Smith, John.  I want to change it to Mr. John Smith for
> the entire list.

0
maistrye (14)
11/29/2006 3:55:56 PM
All personal friends I assume?
-- 
Jim Cone
San Francisco, USA


"Don Smith" <dhsmith@bellsouth.net> 
wrote in message 
I have a long database, 27k with names that I want to mail to.  The
list is last name, first name.  I want to do a global change to first
name, last name.  I also want to add "Mr" to it.  For example:  the
list now is Smith, John.  I want to change it to Mr. John Smith for
the entire list.
0
jim.coneXXX (771)
11/29/2006 3:58:50 PM
Actually they are all Doctors, so M/F is not an issue, or is it?

On Wed, 29 Nov 2006 07:58:50 -0800, "Jim Cone"
<jim.coneXXX@rcn.comXXX> wrote:

>All personal friends I assume?
0
dhsmith (66)
11/29/2006 4:02:54 PM
Sorry,  where do I put this express to make that change?

On 29 Nov 2006 07:55:56 -0800, "Scott" <maistrye@gmail.com> wrote:

>Assuming there is only one comma in each:
>
>="Mr. "&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))&"
>"&LEFT(A1,FIND(",",A1)-1)
>
>Note that some of the women might be offended.... 27k names, all male?
>
>Scott
>
>Don Smith wrote:
>> I have a long database, 27k with names that I want to mail to.  The
>> list is last name, first name.  I want to do a global change to first
>> name, last name.  I also want to add "Mr" to it.  For example:  the
>> list now is Smith, John.  I want to change it to Mr. John Smith for
>> the entire list.
0
dhsmith (66)
11/29/2006 4:09:43 PM
The actual issue is "spam".
Jim Cone
San Francisco, USA


"Don Smith" <dhsmith@bellsouth.net> 
wrote in message 
Actually they are all Doctors, so M/F is not an issue, or is it?


On Wed, 29 Nov 2006 07:58:50 -0800, "Jim Cone"
<jim.coneXXX@rcn.comXXX> wrote:
>All personal friends I assume?
0
jim.coneXXX (771)
11/29/2006 4:13:11 PM
=TRIM(MID(A1&" "&LEFT(A1,FIND(",",A1)-1),FIND(",",A1)+1,255))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Don Smith" <dhsmith@bellsouth.net> wrote in message
news:gk9rm2lhvscrcvjttunqtpb49ki5lf2ddm@4ax.com...
> I have a long database, 27k with names that I want to mail to.  The
> list is last name, first name.  I want to do a global change to first
> name, last name.  I also want to add "Mr" to it.  For example:  the
> list now is Smith, John.  I want to change it to Mr. John Smith for
> the entire list.


0
bob.NGs (282)
11/29/2006 4:22:29 PM
Actually, if it's a mailing, it's not spam... but it is potentially
junk mail.  The concepts are different, even if the activities
sometimes overlap.

Would you be offended to be called Mrs. Don Smith or Ms. Don Smith?
Chances are reasonable that you'd be offended to some degree.  If not,
then don't worry about it.  Otherwise, someone called Mr. Jane Smith
might be offended, resulting in them taking a dim view of your
advertising.  If they are all doctors, the use "Dr.".  If you're not
sure, you might have to find something neutral, but applicable.

The actual usage... create an extra column beside your existing column.
 Put the formula in the first cell beside the first name.  (Adjust it
from referencing A1 if the name isn't in A1).  Copy the formula all the
way down.  Highlight the whole column with the formulas.  Edit -> Paste
Special ... -> Values.  At this point you can delete the original
column.

Note:  If you're not all that proficient with Excel, I'd recommend you
make a backup copy of your spreadsheet, so you can go back to it and
try again.

Scott

Jim Cone wrote:
> The actual issue is "spam".
> Jim Cone
> San Francisco, USA
>
>
> "Don Smith" <dhsmith@bellsouth.net>
> wrote in message
> Actually they are all Doctors, so M/F is not an issue, or is it?
>
>
> On Wed, 29 Nov 2006 07:58:50 -0800, "Jim Cone"
> <jim.coneXXX@rcn.comXXX> wrote:
> >All personal friends I assume?

0
maistrye (14)
11/29/2006 5:21:03 PM
Sorry, I have been playing with this for two hours and am getting
close, but . . . .   If I copy the formula to the first column, and
the names are in the second column, how do I get the formula to work?

On 29 Nov 2006 09:21:03 -0800, "Scott" <maistrye@gmail.com> wrote:

>Actually, if it's a mailing, it's not spam... but it is potentially
>junk mail.  The concepts are different, even if the activities
>sometimes overlap.
>
>Would you be offended to be called Mrs. Don Smith or Ms. Don Smith?
>Chances are reasonable that you'd be offended to some degree.  If not,
>then don't worry about it.  Otherwise, someone called Mr. Jane Smith
>might be offended, resulting in them taking a dim view of your
>advertising.  If they are all doctors, the use "Dr.".  If you're not
>sure, you might have to find something neutral, but applicable.
>
>The actual usage... create an extra column beside your existing column.
> Put the formula in the first cell beside the first name.  (Adjust it
>from referencing A1 if the name isn't in A1).  Copy the formula all the
>way down.  Highlight the whole column with the formulas.  Edit -> Paste
>Special ... -> Values.  At this point you can delete the original
>column.
>
>Note:  If you're not all that proficient with Excel, I'd recommend you
>make a backup copy of your spreadsheet, so you can go back to it and
>try again.
>
>Scott
>
>Jim Cone wrote:
>> The actual issue is "spam".
>> Jim Cone
>> San Francisco, USA
>>
>>
>> "Don Smith" <dhsmith@bellsouth.net>
>> wrote in message
>> Actually they are all Doctors, so M/F is not an issue, or is it?
>>
>>
>> On Wed, 29 Nov 2006 07:58:50 -0800, "Jim Cone"
>> <jim.coneXXX@rcn.comXXX> wrote:
>> >All personal friends I assume?
0
dhsmith (66)
11/29/2006 8:03:13 PM
That's where the Paste Special -> Values comes in.  This converts
formulas to the value they result in.  You can highlight the whole
column with the formulas, and then either Paste Special -> Values
there, or do so in the original column.  At that point, wherever you
pasted it should have the names the way you want them, and you delete
the other column.  Sorry, not sure how else to describe it.  You might
search on the net for an illustrated walk-through for Paste Special.

Scott

Don Smith wrote:
> Sorry, I have been playing with this for two hours and am getting
> close, but . . . .   If I copy the formula to the first column, and
> the names are in the second column, how do I get the formula to work?
>
> On 29 Nov 2006 09:21:03 -0800, "Scott" <maistrye@gmail.com> wrote:
>
> >Actually, if it's a mailing, it's not spam... but it is potentially
> >junk mail.  The concepts are different, even if the activities
> >sometimes overlap.
> >
> >Would you be offended to be called Mrs. Don Smith or Ms. Don Smith?
> >Chances are reasonable that you'd be offended to some degree.  If not,
> >then don't worry about it.  Otherwise, someone called Mr. Jane Smith
> >might be offended, resulting in them taking a dim view of your
> >advertising.  If they are all doctors, the use "Dr.".  If you're not
> >sure, you might have to find something neutral, but applicable.
> >
> >The actual usage... create an extra column beside your existing column.
> > Put the formula in the first cell beside the first name.  (Adjust it
> >from referencing A1 if the name isn't in A1).  Copy the formula all the
> >way down.  Highlight the whole column with the formulas.  Edit -> Paste
> >Special ... -> Values.  At this point you can delete the original
> >column.
> >
> >Note:  If you're not all that proficient with Excel, I'd recommend you
> >make a backup copy of your spreadsheet, so you can go back to it and
> >try again.
> >
> >Scott
> >
> >Jim Cone wrote:
> >> The actual issue is "spam".
> >> Jim Cone
> >> San Francisco, USA
> >>
> >>
> >> "Don Smith" <dhsmith@bellsouth.net>
> >> wrote in message
> >> Actually they are all Doctors, so M/F is not an issue, or is it?
> >>
> >>
> >> On Wed, 29 Nov 2006 07:58:50 -0800, "Jim Cone"
> >> <jim.coneXXX@rcn.comXXX> wrote:
> >> >All personal friends I assume?

0
maistrye (14)
11/29/2006 9:55:25 PM
Reply:

Similar Artilces:

Chart Names
Hi everyone, I'm sorry if this is an easy one but I just can't figure it out. I have a worksheet with 3 charts on it. I need to write some VBA to select each chart in turn and change its axis settings. So I figured the first thing to do is to name the charts so that I can select them properly in the VBA. But how do I name a chart??? If I select the chart I get "Chart Area" in the name box and I can't edit it. Many thanks for any help, Tony M As answered in .programming -- Hold the Ctrl key and click on the chart to select it. Click in the Name Box, type a new ...

Naming a range
I have a such macro : Private Sub Macro1(arkusz As Worksheet) arkusz.Range(arkusz.PageSetup.PrintArea).Copy Sheets("Sheet1").Select Range("A" & Trim(Str(zLastRow))).Select Arkusz50.Paste Application.CutCopyMode = False ActiveWorkbook.Names.Add Name:="A1", RefersToR1C1:=Selection zLastRow = zLastRow + arkusz.Range(arkusz.PageSetup.PrintArea).Rows.Count Range("A" & Trim(Str(zLastRow))).Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell End Sub However the above macro doesnt work (this part: ActiveWorkbook.Names.Add Name:=&quo...

Name Assignment
I need to assign a staff memeber to a group of clients Staff Names Clients Milk Shoe A-F Form Code G-L John DOe M-P The table that contains the client information CREATE TABLE [dbo].[FD__CLIENTS]( [OP__DOCID] [int] NOT NULL, [Fullname] [varchar](68) NULL, [NameF] [varchar](20) NULL, [NameL] [varchar](20) NULL, [NameMI] [varchar](20) NULL CONSTRAINT [PK_FD__CLIENTS] PRIMARY KEY CLUSTERED There is a staff table but there is nothing that ties it to the clients table. So the staf names will have to be hardcoded. Any ideas? On...

How do I remove the sheet name from a named formula?
I would like to use the same name to refer to the same set of cells on different worksheets: SheetTitle=$A$1 but when I try this it reverts to SheetTitle=Sheet1!$A$1 Any ideas how to do it? I could use =IF(,,,) if I knew how to look up the currently active sheet; =IF(SheetName="Sheet1",TRUE,FALSE) Any ideas how to reference the name of the sheet? Well, you could do something like this: SheetTitle=!$A$1 The exclamation point means that the Name will refer to A1 on the active worksheet. Excel uses the SheetName to tell things apart. If I try to give to different A1'...

Last Date 05-15-07
I'm trying to find the last Date a transaction occured for a particular customer and the DMax doesn't seem to be working. Should I be using something else? Forms!Form6!Text2 = DMax("ChkDate", "tblChecks", "ChkCustomerID = " & Forms!Form6!Text0) Thnaks DS Why not create a regular query sorted by Date descending and try that instead. You could use a Dlookup for that. Try something like: Forms!Form6!Text2 = DLookup("ChkDate", "YourQuery", "ChkCustomerID = " & Forms!Form6!Text0) - where yourquery references th...

Re: Excel 2003 plotting graphs in inverse order
Excel 2003 is plotting any type of graph I select in reverse order. I a selecting data ranges normally, yet Excel is creating these upside dow graphs. I thought perhaps I'd inadvertantly changed a setting and trie selecting "Default formatting" on chart type selection, but the defaul is this inverse plotting!! I can select the axis(es) and select plot i reverse order, which generally addresses my problem, except that the the X-axis label winds up on top of the graph. Plot in reverse orde does not fix my root problem, which is that the darn thing is plottin EVERYTHING inversely a...

How to merge fields in vertical order in publisher?
I am using a catalog merge that has 8 fields down and 2 across. I want the fileds that will be inserted to be ordered vertically in one column and then the second column. It automatically orders the field by rows. You will have to setup your data differently. There is a sample Excel file here that shows you how. http://msauer.mvps.org/more_graphics.htm The second item on the left. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "dlee1967" <dlee1967@discu...

Auto populate Email when Name is chosen
I have a list of names which i present in a combo box on the userform. I have an email textbox. I want the email to be auto-populated based on the name chosed. May I ask your help with this? Hi Jerry, Only the first procedure is really relevant in this post the others are to show how you may want to call it but, that entirely depends on your needs. Private sub BuildAndSend has 4 required parameters “ToWhom”, “Subject”,”Body”, and “Send”. Pass the appropriate values to the procedure and it will send or display an email to the user to send. HTH. Private Sub BuildAndSend( _ ...

Custom entity with same display name as out of the box?
I tried to create a new entity "new_InvoiceDDI" with a display name of "Invoice" and it won't allow the display name to be the same as a "out of the box" entity. Why is this? Anyone know a way around it? CRM 4.0 Mark Hi Mark, There's no way around it except renaming the existing invoice entity to something else. One obvious reason is the Outlook Client. It uses the display names to show folders, but folders needs to have a unique name. The web client uses the display names as well, so how can a user differentiate between two entities having the...

Write in Producucts for Orders Fails
Hi All, I'm finding this error a bit of an odd one to be honest so I was wondering if anyone has any ideas on it: I can add write in products in quotes fine. When I convert the quote to an order, or create a new order with write in products they dont appear. When I create a new write in product I get the message: "Record Is Unavailable The record that you are requesting is currently unavailable. Either the record was not found or you do not have sufficient security permissions to view it. " I know I have the permissions as I have every default security role assigned to my a...

Populate combobox with folder names
I had found this code to show folder names that displays in a msgbox, is it possible to get the list of folder names into a combobox? --------------------------------------- Sub ListFolders() Dim fs, f, f1, fc, s Dim folderspec folderspec = "C:\Excel\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.SubFolders For Each f1 In fc s = s & f1.Name s = s & vbCrLf Next MsgBox s End Sub ---------------------------------------- This is for exc...

Instance name must be the same name as the computer name ...
I've seen many posts about this installation issue and ran across it myself during an install on an existing SBS server. When running Select @@servername I received a null answer. I looked in the sysservers table in the master database and found that there was no '0' server listed as I've seen on all other installs of SQL. I took a chance and changed the value to 0, restarted SQL and ran the @@servername and it now came back as the name of the server as expected and I was able to install CRM with no problems. Long term I don't know if this will cause issues with ...

Change the name of an employee class
Is there a way in the SQL table to change the name of employee classes? Patti, Where did you want to change them (cards, trx, history, etc). There are several tables involved. Would it be possible to make new classes starting with the class you want to duplicate as a default. Please let us know what you are trying to accomplish by changing classes. Kind regards, Leslie "Patti Anderson" wrote: > Is there a way in the SQL table to change the name of employee classes? Our naming convention has changed, and requires changing the names of all employee classes...

Ability to close Manufacturing Order by a percentage
Customer would like the ability to close a Manufacturing Order by a certain percentage amount. The system could then apply the percentage to each component line item. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community...

Named Reference
Hi, Can someone tell me what the pros and cons are (if any) of these 2 ways of naming a range. ActiveWorkbook.Names.Add NAME:="HOME", RefersTo:=ActiveCell Range("HOME").Select or Dim HOME As Range Set HOME = ActiveCell HOME.Select Thanks, DaveU It seems to me that the first method creates a named range that is essentially meaningless to the user and is only used for VBA processing. Additionally, if you don't write code to delete the named range it will just sit there in the workbook waiting for somebody to ask what it means. The second alternative is a clea...

"Name Conflict" when Excel 2007 open an old worksheet
Hi all, An "Name Conflict" error message pop-up "Name cannot be the same as a built-in name". That worksheet was created by Excel 2000 and also could open on Excel 2003 without any error message. Change the worksheet file name could not solve the problem after tested. Why? Thank you, Chik "Chik" <c...@netvigator.com> wrote... >An "Name Conflict" error message pop-up "Name cannot be the same as >a built-in name". That worksheet was created by Excel 2000 and also >could open on Excel 2003 without any error message. Change th...

Purchase Order Enhancements Entry
Hello all, We have no had an issue at two customer sites whereby we have noticed a massive performance hit on loading up the Purchase Order Enhancements Entry form on GP10 versus GP9 with the same data. The difference is between an almost instantaneous load on GP9 versus upto three minutes thumb-twiddling on GP10. For those not in-the-know, Purchase Order Enhancements Entry is the cleverly titled form for bulk approving Purchase Orders, when you have approvals switched on. Has anyone else encountered this problem? Cheers, Andrew Andrew, I would run a SQL Profiler with a T-SQL replay pr...

Find a time value in one column based on names in another
Hi - I have a list of names. (40 Different names that may occur 30 - 40 times a piece in column B). In Column C I have a list of times that each person made an entry. Is there a formula that will give me the earliest time that appears in column C for each person on the list in column B? I already have a list of all of the unique names that will appear in column B that I can use as a reference. Example: col A col B col C smith 6:00am jones 5:03pm smith 7:05am jones 4:02pm adams 2:05pm adams 2:33p...

convert from last name, first name TO first name, last name
How can I convert my contacts (as a group menaing all of them at once0 from the last name, first name to first name last name, without having to enter EACH cotnact and perform it almost 400 times? Also, in what format would you suggest trying to print my contacts showing fields name mobile, home, work phone and email address? I assume there is a way to make used defined fields? You'll need to create a post with enough information that others could understand it. How are we to know which field or view you are trying to "convert?" Be specific. I would suggest pri...

Changing display order of primary & secondary axes in Excel charts
I'm trying to create a chart that uses horizontal bars to indicate the ideal stacking of tenants in a building (with each row containing several different boxes, or tenants, depending upon the size of the floor) in Excel 2003. One entry in the data table for this chart is "available floor area" which indicates the total sq. ft. available on each floor; this entry is plotted on the secondary axis, and all the tenants' (sq.ft.) leases are shown on the primary axis. Ideally, objects on the primary axis (bars representing individual tenant leases)should show up as being...

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 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 ...

Edit Licensed to Name
I need to edit my last name in the Office 2004 registration (Splash screen). How can I do this? Thanks In article <news-AC4BCA.12232514102004@msnews.microsoft.com>, news@biggerhammer.org wrote: > I need to edit my last name in the Office 2004 registration (Splash > screen). How can I do this? > > Thanks Take a look at http://www.mcgimpsey.com/macoffice/office/pid.html ...

Name Tags
I'm working on putting together some name tags using Publisher but when I get to the name tags and what one's to use, it will only let me print the same name tag on one sheet. How can I put more then one name on a sheet of name tags? Thanks Use mail merge. Create a database with the names, you can do this in Publisher. Click Mail Merge on the toolbar (Publisher 2000), create Publisher Address List. -- Mary Sauer MS MVP http://office.microsoft.com/clipart/ http://mvps.org/msauer/ "Gideon" <gideonkaine@hotmail.com> wrote in message news...

to create nametags, I want 8 names per sheet, not 1 name 8 times
After using an excell database as the source and mail merging, I am getting a sheet of 8 nametags with the smae person's name instead of 8 different names per sheet. How do I fix this? Are you looking at print preview? There is a bug in Publisher that when you view print preview the same information is on all labels. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "fred" <fred@discussions.microsoft.com> wrote in message news:22F2F053-3985-4D41-8F0B-E1E20119DF92@microsoft.com... > After using an excell datab...

How do I view Pivot Table source data file and field names?
I'd like information on how you can view the name/reference for the source data of a Pivot Table. For Example, in MS Access and Cognos Impromptu you can go to SQL view and see where data in a Query is coming from. It's easy to find the source of formulas in Excel, using the Audit Toolbar. I'd like a way to quickly view the file name (whether an Excel file or external source) and field names that were used to create the Pivot Table. chocolate2346 Wrote: > I'd like information on how you can view the name/reference for the > source > data of a Pivot Table. Fo...