Concatenate to both words in a cell...?

[Zack Donovan]

Does anyone know how I can concatenate so that my modifier appears in front of each word in a cell.


For example, if I wanted to concatenate '+' to a A1 and the cell contained 'Nike Trainers'

I would want it to concatenate to:

'+Nike +Trainers'


Anyone have any tips?

Would be much appreciated!

 

[upsite]

Are you talking about Excel?

 

[Dan_HiHosting]

Yeah, I think he is. Worth mentioning that though.
A workaround would be to export to csv (comma seperated value) file, and then use command line text editing to prepend every word with a +, and then reopen the modified file in Excel. That used to require Linux, but windows has good command line scripting now. A google search should tell you how to do it.

If the words were in separate cells you could just use a formula like ="+"&A1 in cell B1 for example.

 

[awebapart.com]

If the words in the cell are just separated by spaces, then you could use the replace function, replacing " " (space) with " +" (spaceplus), and concatenate the other + at the start of the cell (for the first word, which doesn't have a space before it).

 

[awebapart.com]

Here's the forumula:

=CONCATENATE("+",SUBSTITUTE(TRIM(A1), " ", " +"))

which should work on cells containing from 1 to many words

 

[Zack Donovan]

Very much appreciated all!