Hi there,
I need help for a code that insert space in a string after 10 characters.
for example >> MQGSVTEFLKPRLVDIEQVSSTHAKVTLEPLERGFGHTLG...
out put >> MQGSVTEFLK PRLVDIEQVS STHAKVTLEP LERGFGHTLG ...
Thanks,
Hi there,
I need help for a code that insert space in a string after 10 characters.
for example >> MQGSVTEFLKPRLVDIEQVSSTHAKVTLEPLERGFGHTLG...
out put >> MQGSVTEFLK PRLVDIEQVS STHAKVTLEP LERGFGHTLG ...
Thanks,
Perhaps something like this:
[vba]Sub testing()
Debug.Print InsertSpaceEveryN("MQGSVTEFLKPRLVDIEQVSSTHAKVTLEPLERGFGHTLG", 10)
End Sub
Function InsertSpaceEveryN(strInput As String, n As Long) As String
Dim strTemp As String
Dim lngIndex As Long
For lngIndex = 1 To Len(strInput) Step n
strTemp = strTemp & " " & Mid$(strInput, lngIndex, n)
Next lngIndex
InsertSpaceEveryN = Mid$(strTemp, 2)
End Function
[/vba]
Be as you wish to seem
Hi Aflatoon,
thank you very much for your help. I have one more question that if a string I want to add a space is in range A1, so can I do this?
[VBA]
Sub testing()
Debug.Print InsertSpaceEveryN(A1, 10)
End Sub
Function InsertSpaceEveryN(strInput As String, n As Long) As String
Dim strTemp As String
Dim lngIndex As Long
For lngIndex = 1 To Len(strInput) Step n
strTemp = strTemp & " " & Mid$(strInput, lngIndex, n)
Next lngIndex
InsertSpaceEveryN = Mid$(strTemp, 2)
End Function [/VBA]
It would be:
[vba]Sub testing()
Debug.Print InsertSpaceEveryN(Range("A1").Value, 10)
End Sub[/vba]
Be as you wish to seem
Thank you again!!!
I am sorry that I need to bother you again,
If the data is in A1 and I want to add the spaces (with your code) to A1, if it's possible?
Thanks,
[vba]Sub testing()
Range("A1").Value = InsertSpaceEveryN(Range("A1").Value, 10)
End Sub [/vba]
Be as you wish to seem
I think I must do something wrong.
I place the code to new module and create a button to start the code but nothing happens.
If I add =InsertSpaceEveryN(A1, 10) to certain range then it works.
could you please tell me what could be the problem?
thanks,
Did you assign the testing macro to the button?
Be as you wish to seem
yes I did.
Then I do not see how it would not work assuming the correct sheet is active.
Be as you wish to seem
what I do exactly; add the code to module then create a button on sheet1 after that assign to testing macro. I input data to range A1. The code that I add is
[vba]Sub testing()
Debug.Print InsertSpaceEveryN(Range("A1").Value, 10)
End Sub
Function InsertSpaceEveryN(strInput As String, n As Long) As String
Dim strTemp As String
Dim lngIndex As Long
For lngIndex = 1 To Len(strInput) Step n
strTemp = strTemp & " " & Mid$(strInput, lngIndex, n)
Next lngIndex
InsertSpaceEveryN = Mid$(strTemp, 2)
End Function [/vba]
Nothing is happenning after I click the button.
Anyway thank you for your kind and rapid reply. I will try to find what I did wrongly. If I get it I let you know.
Thanks,
You could always post the workbook here if you get stuck.
Be as you wish to seem
HI hunna,
Keep the function as you have it, except put it in a regular module, along with the testing sub. And change the testing sub command to what Aflatoon provided in post#7 - Then you can call the sub using Call testing from within an activex button, or asign a sheet forms type button to the macro.
You have the wrong version of the testing sub - it is still the debug.print version, not the one I posted subsequently.
Be as you wish to seem
Hi Aflatoon,
Thank you very much, now it works like a charm. I am sorry that I need to ask another question. After adding a space to data, the font color is changed back to default (of that range). If it's possible not to change the original format.
for example >> MQGSVTEFLKPRLVDIEQVSSTHAKVTLEPLERGFGHTLG...
output >> MQGSVTEFLK PRLVDIEQVS STHAKVTLEP LERGFGHTLG ...
disired output >> MQGSVTEFLK PRLVDIEQVS STHAKVTLEP LERGFGHTLG ...
That can be done but it will take a bit of work. I cannot look at that until next week at the earliest, I am afraid.
Be as you wish to seem
thank you very much for your help. It's fine not to change it.
Probably a little klutzy, but seems to work:
[VBA]
Option Explicit
Sub test()
Dim Cell As Range
Dim a_vntCharProps() As Variant
Dim a_vntStringColors As Variant
Dim strOut As String
Dim i As Long
Dim n As Long
Dim lColorIndex As Long
Dim bolIsWorksheet As Boolean
On Error Resume Next
bolIsWorksheet = ActiveSheet.Type = xlWorksheet
On Error GoTo 0
If bolIsWorksheet Then
For Each Cell In Selection
lColorIndex = 9999
ReDim a_vntStringColors(1 To 2, 0 To 0)
If GetChars(Cell, 10, a_vntCharProps) Then
strOut = vbNullString
For i = LBound(a_vntCharProps, 1) To UBound(a_vntCharProps, 1)
strOut = strOut & a_vntCharProps(i, 1)
If a_vntCharProps(i, 2) = lColorIndex Then
a_vntStringColors(2, UBound(a_vntStringColors, 2)) _
= a_vntStringColors(2, UBound(a_vntStringColors, 2)) + 1
Else
ReDim Preserve a_vntStringColors(1 To 2, _
1 To UBound(a_vntStringColors, 2) + 1)
lColorIndex = a_vntCharProps(i, 2)
a_vntStringColors(1, UBound(a_vntStringColors, 2)) = lColorIndex
a_vntStringColors(2, UBound(a_vntStringColors, 2)) = 1
End If
Next
Cell.Value = strOut
n = 1
For i = LBound(a_vntStringColors, 2) To UBound(a_vntStringColors, 2)
Cell.Characters(n, a_vntStringColors(2, i)).Font.ColorIndex _
= a_vntStringColors(1, i)
n = n + a_vntStringColors(2, i)
Next
End If
Next
End If
End Sub
Function GetChars(Rng As Range, n As Long, ary() As Variant) As Boolean
Dim lLen As Long
Dim lCharPos As Long
Dim i As Long
Dim strTmp As String
If Len(Rng.Value) < 11 Or IsNumeric(Rng.Value) Then Exit Function
strTmp = Rng.Value
lLen = (Len(strTmp) \ n) + Abs(CBool(Len(strTmp) Mod n)) - 1 + Len(strTmp)
ReDim ary(1 To lLen, 1 To 2) As Variant
For i = LBound(ary, 1) To UBound(ary, 1)
If Not CBool(i Mod (n + 1)) Then
ary(i, 1) = Chr(32)
ary(i, 2) = -4105
Else
lCharPos = lCharPos + 1
ary(i, 1) = Mid(strTmp, lCharPos, 1)
ary(i, 2) = Rng.Characters(lCharPos, 1).Font.ColorIndex
End If
Next
GetChars = True
End Function
[/VBA]