How do I replace just a few characters?

Hello,
I have an excel worksheet with a street name column.  I need to
rename some names from "Main Street" to "Main St".  I can do it for one
name easily with find and replace, but how do I make it work for an
entire column of different names such as "Easy Street" and "Main
Street".  If I do find "* Street" and replace with "* ST" I get "* ST"
for the output instead of "Easy ST"

What am I missing?


-- 
Tomsriv
------------------------------------------------------------------------
Tomsriv's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37508
View this thread: http://www.excelforum.com/showthread.php?threadid=571598

0
8/14/2006 10:37:49 PM
excel 39880 articles. 0 followers. Follow

3 Replies
138 Views

Similar Articles

[PageSpeed] 0

Do exactly what you are doing. .but instead of pressing replace, press
replace all.

BTW.. do a replace on "STREET" with "ST"


-- 
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35016
View this thread: http://www.excelforum.com/showthread.php?threadid=571598

0
8/14/2006 10:40:56 PM
Don't use the wildcard.

Select the column and.......

Replace what:  Street

Replace with:  St.

Works for me with a column of Main Street, Easy Street and similar.

If you have a name like Street Street I don't know<g>


Gord Dibben  MS Excel MVP


On Mon, 14 Aug 2006 18:37:49 -0400, Tomsriv
<Tomsriv.2cjxcu_1155595208.6647@excelforum-nospam.com> wrote:

>
>Hello,
>I have an excel worksheet with a street name column.  I need to
>rename some names from "Main Street" to "Main St".  I can do it for one
>name easily with find and replace, but how do I make it work for an
>entire column of different names such as "Easy Street" and "Main
>Street".  If I do find "* Street" and replace with "* ST" I get "* ST"
>for the output instead of "Easy ST"
>
>What am I missing?

0
Gord
8/14/2006 10:53:52 PM
Thanks for the quick reply

When I say replace all it doesn't work for different names.
But I found a work around.  If I do space road " ROAD", replace wit
space rd " RD"then it will always find it because their is always 
space between the street name and the suffix

--
Tomsri
-----------------------------------------------------------------------
Tomsriv's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3750
View this thread: http://www.excelforum.com/showthread.php?threadid=57159

0
8/14/2006 11:09:51 PM
Reply:

Similar Artilces:

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Lengthen the state field to 30 characters
We want to lengthen the state field for Customers to 30 characters to enable royal maill addresses to be used and then postcode lookup addin can be used in places such as Gretna where the county(state) is Dumfries and Galloway ---------------- 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 ...

How do I combine multiple columns into just 1 column?
In my excel file, the address is split into 4 columns. I want to combine the 4 columns into 1. In a fifth column, use the concanenate function or amphisand to combine the information. Then drag down to copy for each row. =CONCATENATE(A1,B1,C1,D1) or =A1&B1&C1&D1 HTH "scosus" wrote: > In my excel file, the address is split into 4 columns. I want to combine the > 4 columns into 1. ...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Cannot insert extended ascii character
This article tells about how to insert an extended ascii character: http://office.microsoft.com/en-us/assistance/HA011331361033.aspx The "Alt + decimal code" method works well with my pc at work. But I cannot make that work with my laptop at home (Dell Inspiron 600m, if it matters). Using Office Professional 2003 on Windows XP in both cases. When I type in the code on my laptop, nothing happens. Whether I use the normal laptop number keys, or the ten-key keypad with number lock on, it makes no difference. Can anyone give me any clues? TIA Hi! I don't know why thi...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...

SD03*10 -how do I just get the numbers at the end in cell after *
,I have SD03*10 in a cell and I want in a separate cell, just to have the numbers after the *. Obviously with lots of data, the digits after the * could be 1 to many characters so I cannot use the Left function. Any help would be greatly appreciated so I don't waste any more time! On Wed, 12 May 2010 12:43:01 -0700, Helen Maguire <Helen Maguire@discussions.microsoft.com> wrote: >,I have SD03*10 in a cell and I want in a separate cell, just to have the >numbers after the *. Obviously with lots of data, the digits after the * >could be 1 to many characters so ...

How do I replace just a few characters?
Hello, I have an excel worksheet with a street name column. I need to rename some names from "Main Street" to "Main St". I can do it for one name easily with find and replace, but how do I make it work for an entire column of different names such as "Easy Street" and "Main Street". If I do find "* Street" and replace with "* ST" I get "* ST" for the output instead of "Easy ST" What am I missing? -- Tomsriv ------------------------------------------------------------------------ Tomsriv's Profile: http:/...

Outlook 2000 Just got noticeably slower
I am running Outlook 2000 on an XP SP2 machine. In the past week it has gotten noticeably slower. I note that someone posted a similar question back last december 7th but I couldn't find anyone's responses. The machine has about 2 months of sent mail and I have cleaned out the deleted items folder. It is connected to an Exchange 2003 server. I am running AVG 7.5 and zonealarm but have been for months with this computer. I can't imagine my .ost file is an bigger than a few 100 megs at this point. I have checked for viruses and run 2 different spyware programs to ...

VBA to find filename and replace
Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW mont...

Replacing Character
I need to replace/delete the " >" character at the end of a number... when the data was brought into the excel 1234> was the number that cam it happens in about 7000 lines any suggestions on how to replace it with a blank?? thank -- tico31p ----------------------------------------------------------------------- tico31pl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1040 View this thread: http://www.excelforum.com/showthread.php?threadid=46572 Select the range to fix. Edit|Replace what: " >" (without the quotes with: (leav...

Replacing A Column Of Numbers
Hello, New user of Excel 2007, and Excel in general. Should be simple, but I just can't figure out how. Have a column of numbers in Column A In Column B (row 1)I have the formula of what's in A (row 1) multiplied by 1000 And I have dragged this formula downwards, so I now have in each row B what is in the same row in Column A multiplied by 1000. So far, all is fine. Now, I want to replace what's in Column A by the newly generated values in Column B (row by row replacement) I can't just Cut out everything in Column A, as B then disappears, as, I'v...

Changing character for time enty
Is there a way to change the colon (:) while entering time data? I would like to be able to use the period (.) in my keypad to be recognized as the dividing character for hours and minutes. I am in Excel 2003..... Hi AFAIK this is not really possible (maybe changing the Windows settings could do). You may have a look at: http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Frank Kabel Frankfurt, Germany Tam wrote: > Is there a way to change the colon (:) while entering time data? I > would like to be able to use the period (.) in my keypad to be > recognized as the divi...

Deleting files not just crossing them out?
I there a way that when I delete an email that it is actually delete out of my inbox and not just crossed out -- g.garrett ----------------------------------------------------------------------- g.garrette's Profile: http://www.officehelp.in/member.php?userid=491 View this thread: http://www.officehelp.in/showthread.php?t=124872 Posted from - http://www.officehelp.i g.garrette <g.garrette.2glpie@NoSpamPleaze.com> wrote: > I there a way that when I delete an email that it is actually deleted > out of my inbox and not just crossed out? You are obviously using an IMAP accou...

Find / Replace
In a field, I would like to update all instances of "Daily Billing" to "Rental Payment - Daily Billing". The Replace function works the first time, but when I add data to the file, and run the query again, it updates the fields which have already been upated to "Rental Payment - Rental Payment - Daily Billing". I can see why it is doing this, but is there a way for the update query to look at just the first 11 characters of the field and only perform the update if it finds the "Daily Billing" text there? Thanks. Use "Daily Billing" as ...

Paginating an entire workbook, not just a worksheet
How do you set up Excel to print consecutive page numbers when printing an entire workbook? I can only find instructions per worksheet. Thank you! Group (Select) you sheets Arlene Right click on a sheet tab and choose Select all Then it will do what you want Don't forget to ungroup -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Arlene" <arlenemthompson@yahoo.com> wrote in message news:137c01c381e6$022adad0$a401280a@phx.gbl... > How do you set up Excel to print consecutive page numbers > when printing an entire workbook? I can o...

Exporting just one rule
Hi, How do I export just one rule? I have a very complex rule that I have to replicate to all users. Thanks. As far as I know, no. What you can do is this: - export all your rules to a file - delete all the rules but the one - export your rules (now only one) to another file - re-import your rules that you exported in Step 1 "Microsoft" <g2@bla.cl> wrote in message news:%23B%23VrJp0GHA.1256@TK2MSFTNGP04.phx.gbl... > Hi, > How do I export just one rule? > I have a very complex rule that I have to replicate to all users. ...

how do I protect just 1 column.
I have a spread sheet that I need to protect just on column. The person with the password will be able to enter data in that column. Is there a way for me to this? Highlight the column, Select Format, Protection and check Locked. You may have to select the entire worksheet first and unlock all the cells. The Porotect the Worksheet and allow users to selct unlocked cells. carol Wrote: > I have a spread sheet that I need to protect just on column. The person > with > the password will be able to enter data in that column. Is there a way > for > me to this? -- jahoobob...