View Full Version : How to delete change keywords separated by comma into a cell?

2012-02-05, 09:38 AM
Hi folks, nice to meet you all.

Lets suppose I have the cell A1 with 100 words or more separated by comma, but the words can be composed word or double words such as "jump up", "Old age","sunny day", or more "holding a face", but I want to find these composed words or double/triple+ words with space that are between commas, and put them between double-quotes.

So, as example let's suppose my cell A1 was as:
[A1] word1, jump up, word3, word4, Old age, word6, Sunny day, holding a face,...word100

Want the result as

[A1] word1, "jump up", word3, word4, "Old age", word6, "Sunny day", "holding a face",...word100

It can be a formula on B1 or a trick by using find&replace.

Thanks in advance.

Bob Phillips
2012-02-05, 01:19 PM
Dim vItems As Variant, vItem As Variant
Dim i As Long

With ActiveCell

vItems = Split(.Value, ",")
For i = LBound(vItems) To UBound(vItems)

vItems(i) = Trim(vItems(i))
If InStr(vItems(i), " ") > 0 Then

vItems(i) = Chr(34) & vItems(i) & Chr(34)
End If
Next i

.Value = Join(vItems, ",")
End With

2012-02-05, 01:29 PM
Hi Bob, thanks for your reply. I'm sorry but I'm newbie with VBA, Macro stuffs, and it seems a VBA, right?
What I remember I hit ALT+F11 and paste the code in VBA/Macro, but I forgot how do I call this code in the cell B1?


Bob Phillips
2012-02-05, 02:44 PM
You just run it, you don't run it in a cell.