Welcome to WebmasterWorld Guest from 54.145.235.72

Forum Moderators: bill

Find and Replace on Excel

Per cell, not whole page

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

5+ Year Member



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)

10+ Year Member



Can you just select the cells and then do a find and replace?
10:01 pm on May 22, 2006 (gmt 0)

5+ Year Member



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)

5+ Year Member



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)

WebmasterWorld Senior Member billys is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

5+ Year Member



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)

WebmasterWorld Senior Member billys is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

WebmasterWorld Senior Member billys is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month