Adding Character every 3rd character

[surya prakash]

Hi,
Good afternoon.

I am wondering if it is possible to solve the following problem:

Col A of excel will have following text, col b should have results

Number of character jump = 3
Enter Character = #

Peter Herson Pet#er #Her#son
Michael Johnson Mic#hae#l J#ohn,son
.
.
.
.
.

If the character jump is 3, vba should enter the character # every 3 characters.

thanks
prakash

[Jacob Hilderbrand]

Try this macro:

Option Explicit
 
Sub Macro1()
Dim i               As Long
Dim j               As Long
Dim LastRow         As Long
Dim n               As Long
Dim StartText       As String
Dim EndText         As String
Dim Jump            As Long
Dim Prompt          As String
Dim Title           As String
Prompt = "What is the jump factor?"
    Title = "Jump Factor Input"
    Jump = InputBox(Prompt, Title)
    If Jump < 1 Then
        Exit Sub
    End If
    LastRow = Range("A65536").End(xlUp).Row
    For i = 1 To LastRow
        StartText = Range("A" & i).Text
        n = Len(StartText)
        EndText = ""
        If n > Jump Then
            For j = Jump + 1 To n + Jump Step Jump
                EndText = EndText & "#" & Mid(StartText, j - 3, Jump)
            Next j
            Range("B" & i).Value = Mid(EndText, 2, Len(EndText))
        Else
            Range("B" & i).Value = StartText
        End If
    Next i
End Sub

[Richie(UK)]

Hi,

I went for a similar approach to that used by Jacob but opted for a function, this lets you pass the seperator and jump values as arguments and means that you can use it as a function on the worksheet.

Call with : =SPChar(A3,$A$1,$A$2)
where A1 houses the seperator and A2 the jump and the values start in A3.

Sub Main()
    MsgBox SPChar("Peter Herson", "#", 3)
End Sub

Function SPChar(strIn As String, strSep As String, lJump As Long) As String
    Dim lCnt As Long, lRem As Long
If Len(strIn) > lJump Then
        lRem = Len(strIn) Mod lJump
        For lCnt = 1 To (Len(strIn) - lRem) / lJump
            SPChar = SPChar & Mid(strIn, 1 + ((lCnt - 1) * lJump), lJump) & strSep
        Next lCnt
        SPChar = Left(SPChar, Len(SPChar) - 1)
        If Not lRem = 0 Then
            SPChar = SPChar & strSep & Mid(strIn, 1 + ((lCnt - 1) * lJump), lJump)
        End If
    Else
        SPChar = strIn
    End If
End Function

HTH

[surya prakash]

Hello DRJ,

I am having problem when I set jumpvalue as 1 or 2

thanks

prakash

[Jacob Hilderbrand]

Try this one.

Option Explicit
  
 Sub Macro1()
Dim i               As Long
     Dim j               As Long
     Dim LastRow         As Long
     Dim n               As Long
     Dim StartText       As String
     Dim EndText         As String
     Dim Jump            As Long
     Dim Prompt          As String
     Dim Title           As String
Prompt = "What is the jump factor?"
     Title = "Jump Factor Input"
     Jump = InputBox(Prompt, Title)
     If Jump < 1 Then
         Exit Sub
     End If
     LastRow = Range("A65536").End(xlUp).Row
     For i = 1 To LastRow
         StartText = Range("A" & i).Text
         n = Len(StartText)
         EndText = ""
         If n > Jump Then
             For j = Jump + 1 To n + Jump Step Jump
                 EndText = EndText & "#" & Mid(StartText, j - Jump, Jump)
             Next j
             Range("B" & i).Value = Mid(EndText, 2, Len(EndText))
         Else
             Range("B" & i).Value = StartText
         End If
     Next i
End Sub

[brettdj]

Or using RegExp

If you have a lot of data in the A column it will be quicker to dump the output to variant array and then to Column B. If the dataset is not large then a simple loop should be fine

Cheers

Dave

Sub RepChar()
     Dim Jump As Integer, CharAct As String
     Dim Myrange As Range, c As Range
     Dim RegEx As Object
Jump = InputBox("What is the jump factor?")
     CharAct = InputBox("Enter Character")
Set RegEx = CreateObject("vbscript.regexp")
     With RegEx
         .Pattern = "(.{" & Jump & "})"
         .Global = True
     End With
Set Myrange = Range(Range("a1"), Range("a65536").End(xlUp))
     For Each c In Myrange
         c.Offset(0, 1) = RegEx.Replace(c.Value, "$1" & CharAct)
     Next
 End Sub

[surya prakash]

Hello Cheers,

Can you please explain your concept

cheers/ prakash

[surya prakash]

Hello Dave,
Can you please explain your RegExp concept.

thanks
prakash