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

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.

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