How to change words in excel cells?

[Jerry]

I have a group of cells filled with words in excel. I need to put a
space after the 3rd charactor in each word. How can i do it using VBA?
thanks

 

[merjet]

Does each cell have one word or more than one? In other words, how
many spaces do you want to insert?

If only one word in a cell, then:

Sub macro1()
Dim rng As Range
Dim c As Range
Set rng = Sheets("Sheet1").Range("A1:A6")
For Each c In rng
c = Left(c, 3) & " " & Right(c, Len(c) - 3)
Next c
End Sub

Hth,
Merjet

 

[okrob]

I have a group of cells filled with words in excel. I need to put a
space after the 3rd charactor in each word. How can i do it using VBA?
thanks

Does each cell you want 'fixed' contain only one word, or are there
multiple words in each cell that you want fixed.

 

[Jerry]

Thank you very much! it is exactly what I want

 

[Jerry]

Does each cell you want 'fixed' contain only one word, or are there
multiple words in each cell that you want fixed.

i simplified my problem. Each cell contains fixed length word. I need
to insert spaces into couple of places (fixed positions)

 

[NickHK]

Jerry,
Maybe you can adapt this:

Public Function InsertChars(argWord As String, CharsToInsert As String,
ParamArray AtPositions() As Variant) As String
Dim i As Long
Dim Temp As String
Dim LastPos As Long

LastPos = 1
For i = LBound(AtPositions) To UBound(AtPositions)
Temp = Temp & Mid(argWord, LastPos, AtPositions(i) - LastPos) &
CharsToInsert
LastPos = AtPositions(i)
Next

InsertChars = Temp & Right(argWord, Len(argWord) -
AtPositions(UBound(AtPositions)) + 1)
End Function

And call it with
=InsertChars("LongWordNeedsCharsInserted","@",5,9,14,19)

Long@Word@Needs@Chars@Inserted

NickHK

 

[Jerry]

Thanks, very nice function. Saved for future use. Thanks again