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 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

Thank you very much! it is exactly what I want

merjet wrote:
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

On Feb 6, 9:03 pm, "Jerry" wrote:
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.

On Feb 6, 10:20 pm, "okrob" wrote:
On Feb 6, 9:03 pm, "Jerry" wrote:

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.


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

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" wrote in message
ups.com...
On Feb 6, 10:20 pm, "okrob" wrote:
On Feb 6, 9:03 pm, "Jerry" wrote:

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.


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

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

On Feb 7, 12:37 am, "NickHK" wrote:
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" wrote in message

ups.com...



On Feb 6, 10:20 pm, "okrob" wrote:
On Feb 6, 9:03 pm, "Jerry" wrote:

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.

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

- Show quoted text -