Replace a comma with a period in a cell containing a lastname, first name, middle i

Hello - I am trying to clean some data and need to change all of my
names from

McLaughlin, Victor, (i.e, comma) W

to

McLaughlin, Victor.(i.e., period) W

Is there an extract and replace formula  or method of som sort (in
excel or access) that will allow me to pull the first comma from the
right and replace it with a period.

Thanks for any suggestions!
0
js2k111 (48)
3/15/2008 3:30:51 PM
excel 39879 articles. 2 followers. Follow

2 Replies
575 Views

Similar Articles

[PageSpeed] 45

Select the cells you want to change and run this tiny macro:

Sub comma_tose()
For Each r In Selection
    v = StrReverse(r.Value)
    r.Value = StrReverse(Replace(v, ",", ".", 1, 1))
Next
End Sub

For example:
a,b,c,d
will be changed to:
a,b,c.d
-- 
Gary''s Student - gsnu2007f


"Mike C" wrote:

> Hello - I am trying to clean some data and need to change all of my
> names from
> 
> McLaughlin, Victor, (i.e, comma) W
> 
> to
> 
> McLaughlin, Victor.(i.e., period) W
> 
> Is there an extract and replace formula  or method of som sort (in
> excel or access) that will allow me to pull the first comma from the
> right and replace it with a period.
> 
> Thanks for any suggestions!
> 
0
GarysStudent (1572)
3/15/2008 4:54:00 PM
On Mar 15, 11:54=A0am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Select the cells you want to change and run this tiny macro:
>
> Sub comma_tose()
> For Each r In Selection
> =A0 =A0 v =3D StrReverse(r.Value)
> =A0 =A0 r.Value =3D StrReverse(Replace(v, ",", ".", 1, 1))
> Next
> End Sub
>
> For example:
> a,b,c,d
> will be changed to:
> a,b,c.d
> --
> Gary''s Student - gsnu2007f
>
>
>
> "Mike C" wrote:
> > Hello - I am trying to clean some data and need to change all of my
> > names from
>
> > McLaughlin, Victor, (i.e,comma) W
>
> > to
>
> > McLaughlin, Victor.(i.e.,period) W
>
> > Is there an extract andreplaceformula =A0or method of som sort (in
> > excel or access) that will allow me to pull the firstcommafrom the
> > right andreplaceit with aperiod.
>
> > Thanks for any suggestions!- Hide quoted text -
>
> - Show quoted text -

Thanks Gary
0
js2k111 (48)
3/16/2008 4:07:01 PM
Reply:

Similar Artilces:

How to get full address in "To" using first few letters of name
At one time, I was able to automatically get the full email address automatically entered in the "To' line after entering just the first few letters of the contact name. Now this only happens when I send another message to the same person I previously sent. How can I get this to work for all contacts? Autocompletion has never used your Contacts. It only uses a cache of previous recipients. -- Russ Valentine "bobengel" <bobengel@discussions.microsoft.com> wrote in message news:D279BE8E-33AE-40EC-9E28-EB614B8A292C@microsoft.com... > At one time, I...

Replaced motherboard due to hardware failure, it's not identical. Repair installation of Windows XP completed but now stuck in Logon-Activation loop.
Dear Group, An ASUS motherboard failed in a Win XP Pro pc belonging to a friend of mine, so I have replace it for her. Asus no longer make this mobo, it's not available so I bought a board from ASrock with the same socket, however the ASUS board had NVidia chipset while the ASRock uses AMD chipset. It's socket 939 and these are hard to come by now. Windows XP repair installation has been completed, it starts normally and offers logon to the pc or the domain controller. I select the pc and after a couple of seconds a Windows Product Activation message appears: "...

not all cells are locked when protect is true for work sheet
not all cells are locked when protect is true for work sheet how can i fix that ? this is how i lock ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True some cells with comments or empty cells are not protected... how can i fix this? Alexandre, sounds like some of the cells are not locked, select all the cells then format, cells, protection, lock -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Alexan...

list numbers in a block of cells
Is there a way to list the values in a block of cells. 400 600 800 350 200 625 250 222 125 9958 226 123 Like: 400 600 800 350 200 625 250 222 125 9958 226 123 Assume source data in A1:C4 Put in say, E1: =OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) Copy E1 down to E12 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeff Klein" <jklein@nospam> wrote in message news:%23PyCLFqPIHA.5140@TK2MSFTNGP05.phx.gbl... > Is there a way to list the values in a block of cells. > > 400 600 800 > 350 200 6...

How do I find out what are Organization Name is?
I need our MS CRM 3.0 Organization Name in order to install an add on product. I've looked around the CRM app, looked at some files on the WebServer, and looked at the CRM entries in the registry, but can't find anything. Can someone tell me where in CRM or where on the CRM WebServer I might find out the exact name we used as our Organization Name? Thanks! Nevermind! I found it in the OrganizationBase table in SQL. "Bacons" wrote: > I need our MS CRM 3.0 Organization Name in order to install an add on > product. I've looked around the CRM app, looked ...

Getting Procedure Name
Can I get the name of a procedure from within the procedure? In my error handler, I write the error to an error table. I'd like to write the name of the procedure that's writing the error. But, rather than customizing each error handler with the procedure name, it would be nice to be able to call a system variable or function that gives me the procedure name and module name. Is that possible? Unfortunately, VBA does not expose the name of the executing procedure, nor the name of that module. In case you are not aware, there is a great little utility you can download from: ...

"indexing" names in Outlook 2003
This strikes me as a setting that can be enabled, but I'll be damned if I can find it... Presently, when I enter a new contact, Outlook indexes it in my contact list as first name, last name, so it is NOT indexed alphabetically last name first as desired. How can I set up the default indexing to be last name, first name ? TIA Control Panel->Mail Icon->Accounts->Directories->View or Change->Set your order from there. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be d...

return cell reference
I am using the DMAX function to find the maximum data value within a certain range of dates. I now need to find the date associated with that maximum value. I am dealing with streamflow data in 5-minute intervals, so there are many peaks in the data. I am looking at specific events by specifying a time range to search in. I have two columns, one column of dates and times (mm/dd/yy hh:mm) and one column of water depth. I have tried DGET (e.g., DGET(R104:T65000, "datetime",R2:U3)) to return the date assocuated with the maxium found by DMAX, but the maximum value may be found...

couting occurence using multiple dates in single cell
Hi, I need to count the no. of meeting that took place in each month. My worksheet is January Feb to Dec column A column B Column C to Column M Column C (data) 1 name no of meeting No of meeting 2 Jack 1/1/09, 1/3/09, 2/1/09, 2/5/09, 3/1/09.4/1/09 3 Peter 2/1/09, 3/1/09,3/15/09 4 Paul 3/1/09, 4/1/09, 4/2/09 is there a way to count the ...

compare date range with date in columns and input data from another cell
I have a spreadsheet that includes the following: 2 columns (d and e) with a beginning and end date, a column with a dollar amount (rent: g) and. 24 columns across (Jan - Dec for two years:- n - ak). The 24 columns across contain the date ie. (01/01/2012, 02/01/2012, and so on). I am wanting to compare the range of D - E with each column across. When it meets the criteria it will put the amount in G in each column that is BETWEEN the date range. For example: if the beginning date is 01/01/2012 (d), the ending date is 06/01/2013 (e), and rent is $3000 (g), I would like Jan - Dec 2012 and Ja...

Replacement of old product codes
HI, I have a Productcode table with fields Oldcode and Newcode. Now I need to replace the ProductCodes in the Order table, the old Codes with the corresponding New codes. How can i do this other than tediously using Find & replace? Is there a way i could create some Update query to pick up from the Product table and replace? Thanks for any help. Ramesh Create a query with your Order table and your Productcode table linked on the prod code in the order table to the old code in the productcode table. Add just the prod code from your order table into the query grid. Change the ...

referencing the cell above
Hi I have a kin a formula wich gets the value from the cell above and then add some. What I get is an list of values incrementing. When I delete one row in this list, all cells below 'crashes' with #REF!. What I need is that for the formulas to always look one cell up, even if I delete a row. Now it seems that if I delete Row8, then when Row9 become Row8 it reference to it self and then ov course goes bananas... Hope I made myself understandable ;-) Any ideas folks? -------- stuhag --------- Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and as it does...

Persons display name is different in Inbox & Sent Items
Hi I'm using O.2003 Inbox is displayed Surname,Forename & in Sent it's the other way around. This makes it awkward for searching etc. In the contact it's displayed Forename,Surname in all the boxes (Full Name, File As, Display as) Is there a way to change this so both future incoming & already received emails are display as I want? Cheers Dave F. Please note this email has been cross-posted to relevant NG's to obtain the quickest, most accurate answer. No, because the sender -- not you -- controls what display name you see = in the Inbox.=20 --=20 Sue Mosher, ...

Worksheet name in cell
Hi, Is it possible to set the worksheet name for a cell. Eg. In cell A1, how to set A1="Sheet1" if Sheet1 is the worksheet name. If the worksheet name changes to "abc" then A1 should reflect "abc". Any help in this regard is appreciated. Thank You. Regards, Giri Giri, Try =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Giri" <giri_here@yahoo.com> wrote in message news:0...

Select max value from 9 cells, copy cell col heading to other cell
Hi, I've selected the maximum value from 9 cells in a row using: =IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3,DY3)) and placed that max value in cell DZ3. I now want to auto-insert the column heading from above that max value cell (DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel 2003). Can anyone help with a formula please? Regards, Steve. You could shorten your formula in DZ3 to: =IF(BA3="","",MAX(DQ3:DY3)) Si...

leading zeros as a cell format
Hi All, I have an issue with formating cells. I have a permit number that has leading zeros sometimes, so I must display them when entered. I have tried 0?-??????? and 0#-####### but no luck. Heres is what my permit number look 01-2345678 & 02-8765432. thanks, Dave Format as text. "Dave" <davidstevans@gmail.com> wrote in message news:4c0dd9d3-5785-44cd-9d1b-a5f0d2280e28@u16g2000pru.googlegroups.com... > Hi All, > > I have an issue with formating cells. I have a permit number that has > leading zeros sometimes, so I must display them when entered. I have &...

How do I make a number have leading zeros to fill width of cell?
I am trying to format existing data so that instead of being a set amount of digits, say 2 for example, it fills out the column based on its width, say 6, with leading zeros. Example - I have 55122 in a cell that has a width of 6, and I want it to say 055122. I know that changing the cell format to text and re-entering the data would fix this problem, but I want a way to change the data without re-entering it. You can use a custom format of 000000, but by width, do you mean that if the cell is widened it gets extra leading digits? -- HTH RP (remove nothere from the email address if m...

Mny 2003/2004 Accounts with -#0 in their name
I noticed that somehow phantom accounts are created with -#0 at the end of the name. For example, my account Wallet has a phantom account Wallet-#0. The transactions in the Wallet-#0 account are the same as transactions in the Wallet account except that the Wallet-#0 account's last transaction is about 2 or three months earlier than the Wallet account. In other words the -#0 is missing the latest transactions. Also the Wallet-#0 account does not appear to have "transfer" transactions categorized as transfers to another account. But I have not verified this with every transaction....

Copy named range based on variable
Is it possible to copy a named range based on a variable? I have a number of named ranges which I need to copy based on the results of a cell value. My whole routine is nearly 500 lines of code, so I've only included the relevant bits in this message. For example, I have the following lines at the start of my routine: With Worksheets("Lookup") Set MTS215Vision30 = .Range("MTS2.16Vision30") Set MTS216Vision30 = .Range("MTS2.16Vision30") End With and want to copy one of these based on the value of cell which could currently co...

Cell always equals 'C: regardless of computer
I have a file in which the cells reference the C drive. If I open that file on another computer it then changes to be the drive for the computer I last edited it on, or the network file letter (if that is the correct way to put it). Is there a way to always keep it referencing the "C" drive no matter which computer opens it or edits it? -- David P. ...

Mary Sauer: Blue File Names
Mary Sauer wrote: > In the Windows folder? They are usually updates, service packs and the like. If > you have "hidden folders" enabled you will see them. > -- > Mary Sauer MSFT MVP > http://office.microsoft.com/ > http://msauer.mvps.org/ > news://msnews.microsoft.com Hi Mary, thanks for the fast reply. When I open up Microsoft Publisher 2002, then click on "FILE", then click on "OPEN", then click on one of my file folders, some of the font colors of the publisher document file names are in the bright color of blue while all the others...

Recovering replacement file
Is it possible to recover a file that was replaced? I deleted a document, went to the trashcan and hit recover, then realized I replaced it over the CORRECT document, is there any way to recover this or am I screwed? It was 15 excel pages!!! HELP!!! Hi Reen, Unless you have a backup you cannot recover it sorry "Reen" wrote: > Is it possible to recover a file that was replaced? I deleted a document, > went to the trashcan and hit recover, then realized I replaced it over the > CORRECT document, is there any way to recover this or am I screwed? It was > 15 exce...

NEED Help With numbering autofill cells
Ok im having bad day got Drain Bamage In say colunm A rows 1 tru 100 I want to number these cells 1 tru 100 in column C I want to number rows 1 tru 100 as 101 thru 200 by physically typing these numbers in each cell as data. A1 is 1 A2 is 2 A99 is 99 A100 is 100 C1 is 101 C100 is 200 I know that you can use auto fill or some thing so you dont have to type each number in. You first say type in 1 2 3 and then select a range and it auto fills it.. and inserts 4 thru 100 I hope you can understand this See your other thread -- Regards Ron de Bruin http://www.rondebrui...

Skip condition if cell is blank
I have 4 conditions 1) If Sheets("Details").Range("H" & lngRow) > Date - 90 And _ 2) Sheets("Details").Range("F" & lngRow) = Sheets("Search").Range("C2") And _ 3) Sheets("Details").Range("J" & lngRow) = Sheets("Search").Range("C4") And _ 4) Sheets("Details").Range("I" & lngRow) = Sheets("Search").Range("E4") Then if Sheets("Search").Range("E2") is blank then condition 1 should be skipped if Sheets("Sear...

disable cell #2
Greetings, I'm sure this is possible but I can't figure it out. How can I disable a cell based on input in another cell. Example: A1=Powered A2=Glider A3= A4= If an "X" is placed in A3, I want A4 disabled (locked). TIA Hi not completely save but - select cell A4 - goto 'Data - Validation -. custom - enter the formula =A3<>"X" Note: This does not prevent copying data to this cell. If you need more security you have to use VBA (an event procedure) -- Regards Frank Kabel Frankfurt, Germany JoeMNY wrote: > Greetings, > > I'm sure this...