homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Microsoft / Microsoft Windows OS (XP/NT/Vista/Windows 7/8/9/10)
Forum Library, Charter, Moderators: bill

Microsoft Windows OS (XP/NT/Vista/Windows 7/8/9/10) Forum

Find and Replace on Excel
Per cell, not whole page

 8:05 pm on May 22, 2006 (gmt 0)

Im trying to find a way to find and replace per cell in Excel.

What I have is this example...

word1 word2 word3

In one colum I need the space, but in another I need the space to be replaced by a +

The spreadsheet is a template so the words change requently. Some have 3 spaces some have just 1 and some have none. I can figure out how to do 1 but if there are more than one I cant. I did a FIND and then just deleted the space. If it has none, then it gives an error.

thx in advance!



 8:23 pm on May 22, 2006 (gmt 0)

Can you just select the cells and then do a find and replace?


 10:01 pm on May 22, 2006 (gmt 0)

Not really. What I have is on one sheet I have a list of cities and states. On another sheet I have a list of keywords. So I can concat them easily, but to put in this code in the URL for my server to pull the right page, the format needs to be in a city-state, but the problem is if there is a space in the city, i.e. los angeles, there needs to be a + inplace of the space.

I do this on a large scale multiple times a day and wondering if there was a way to ease the work load.


 10:06 pm on May 22, 2006 (gmt 0)

I guess what I really need is a line of code that "IF cell contains a space, then REPLACE with +"... but easier said then done


 10:46 pm on May 22, 2006 (gmt 0)

I'm not sure I completely understand what you're trying to do...

Let's say you have the word "Las Vegas" in cell A1

Does this do what you want it to do:

=CONCATENATE(LEFT(A1,FIND(" ",A1)-1),"+",MID(A1,FIND(" ",A1)+1,10))

If you copy the formula correctly, it will return Las+Vegas. Just make sure the "10" is long enough in the MID function.


 11:24 pm on May 22, 2006 (gmt 0)

Almost, but many thanks!

It concats the phrase if it has 1 space to a +. However, if there is no space, then it gives an error, and if there are two spaces, it does the first one.

Thanks for the help!


 11:51 pm on May 22, 2006 (gmt 0)

Try this, same example...

=IF(ISERR(CONCATENATE(LEFT(A1,FIND(" ",A1)-1),"+",MID(A1,FIND(" ",A1)+1,10)))=TRUE,A1,CONCATENATE(LEFT(A1,FIND(" ",A1)-1),"+",MID(A1,FIND(" ",A1)+1,10)))

This handles the error correctly. Can you figure out how to handle the second space?


 12:46 am on May 23, 2006 (gmt 0)

There has to be a better way, again, make sure MID is long enough...

=IF(ISERR(FIND(" ",A1))=TRUE,A1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1))=TRUE,CONCATENATE(LEFT(A1,FIND(" ",A1)-1),"+",MID(A1,FIND(" ",A1)+1,20)),CONCATENATE(LEFT(A1,FIND(" ",A1)-1),"+",MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1))+1),"+",MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,20))))

Worked for me.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Microsoft / Microsoft Windows OS (XP/NT/Vista/Windows 7/8/9/10)
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved