How to change words in excel cells?

  • Thread starter Thread starter Jerry
  • Start date Start date
J

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
 
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
 
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
Click to expand...

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


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
Click to expand...

NickHK
 
Back
Top