Find and Replace on Excel

Per cell, not whole page

[FidelityAssets]

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!

[FromBelgium]

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

[FidelityAssets]

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.

[FidelityAssets]

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

[BillyS]

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.

[FidelityAssets]

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!

[BillyS]

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?

[BillyS]

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.