Microsoft Excel Webinar

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:
    VB:
    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 
    
    
    Formatting tags added by mark007
    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?
    VB:
     
    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 
    
    
    Formatting tags added by mark007

  4. #4
    It would be:
    VB:
    Sub testing() 
        Debug.Print InsertSpaceEveryN(Range("A1").Value, 10) 
    End Sub 
    
    
    Formatting tags added by mark007
    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
    VB:
    Sub testing() 
        Range("A1").Value = InsertSpaceEveryN(Range("A1").Value, 10) 
    End Sub 
    
    
    Formatting tags added by mark007
    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

    VB:
    Sub testing() 
        [COLOR=blue]Debug.Print[/COLOR] 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 
    
    
    Formatting tags added by mark007
    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:

    VB:
    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 
    
    
    Formatting tags added by mark007

Posting Permissions

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