CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulagoogle-sheets-formula
user3347814
user3347814

Reputation: 1143

Add a plus sign to every word in a cell using Excel or Google SpreadSheets formula

I have a column with the name of different products, for example:

   A
1 red car
2 blue whale
3 red carpet in the closet
4 star wars dvd extra especial thing
5 whatever with your however

I would like in column B:

    B
1 +red +car
2 +blue +whale
3 +red +carpet +in +the +closet
4 +star +wars +dvd +extra +especial +thing
5 +whatever +with +your +however

I´m using Google Spreadsheets, but a solution to Excel would work just fine!

If the only way to accomplish that is using a custom formula, I would prefer a Google Spreadsheets formula. Although, I would really like to know a native formula if possible.

Upvotes: 4

Views: 23371

Answers (2)

Wicket
Wicket

Reputation: 38373

To avoid have to fill-down and have to repeat this as new data is entered, on Google Sheets try

=ArrayFormula("+"&REGEXREPLACE(FILTER(E:E,LEN(E:E))," "," \+") )

Upvotes: 3

Excel Hero
Excel Hero

Reputation: 14764

Here you go. Put this in cell B1:

="+"&SUBSTITUTE(A1," "," +")

Now copy downward.

Upvotes: 7

Related Questions