Excel Hints

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 24

Thread: Solved: insert space in a string after n characters

  1. #1

    Solved: insert space in a string after n characters

    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,

  2. #2
    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

  3. #3
    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]

  4. #4
    It would be:
    [vba]Sub testing()
    Debug.Print InsertSpaceEveryN(Range("A1").Value, 10)
    End Sub[/vba]
    Be as you wish to seem

  5. #5

  6. #6
    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,

  7. #7
    [vba]Sub testing()
    Range("A1").Value = InsertSpaceEveryN(Range("A1").Value, 10)
    End Sub [/vba]
    Be as you wish to seem

  8. #8
    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,

  9. #9
    Did you assign the testing macro to the button?
    Be as you wish to seem

  10. #10

  11. #11
    Then I do not see how it would not work assuming the correct sheet is active.
    Be as you wish to seem

  12. #12
    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,

  13. #13
    You could always post the workbook here if you get stuck.
    Be as you wish to seem

  14. #14
    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.

  15. #15
    Hi Aflatoon and frank_m,

    thanks for your help. I have attached a worksheet, please have a look?

    Thanks,
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  16. #16
    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

  17. #17
    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 ...

  18. #18
    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

  19. #19
    thank you very much for your help. It's fine not to change it.

  20. #20
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •