PDA

View Full Version : [SOLVED] Adding Character every 3rd character



surya prakash
01-13-2005, 03:07 AM
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
01-13-2005, 03:41 AM
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)
01-13-2005, 05:56 AM
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
01-17-2005, 02:39 AM
Hello DRJ,

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

thanks

prakash

Jacob Hilderbrand
01-17-2005, 05:57 AM
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
01-17-2005, 06:13 AM
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
01-17-2005, 10:04 AM
Hello Cheers,

Can you please explain your concept

cheers/ prakash

surya prakash
01-18-2005, 09:24 PM
Hello Dave,
Can you please explain your RegExp concept.

thanks
prakash