Add spaces to a string

  • Hi All,


    I want to increase the length of the string in Cell A10 from 71 to 450, am I even close with this code


    Code
    Dim OwnerOfExtract As String
    
    
    OwnerOfExtract = A10.Value
    
    
    If Len(OwnerOfExtract) <> 450 Then
        Mid(OwnerOfExtract, 70) = "1" & Spaces(379)
        MsgBox ("Header fixed")
        End If
  • Re: Add spaces to a string


    incalthis,



    If you want to make an existing string have a length of 450 characters then use this loop:


    Code
    strCellValue = Worksheets("Sheet1").Range("A10")
    do len(strCellValue ) < 450
         strCellValue = stringVariableHere & " " 
    loop
    Worksheets("Sheet1").Range("A10") = strCellValue
  • Re: Add spaces to a string


    Thanks for the advice. When I run it I get a 'subscript out of range' error, have I done it wrong.


    Code
    Dim OwnerOfExtract As String
    
    
    OwnerOfExtract = Worksheets("Sheet 1").Range("A10")
    Do While Len(OwnerOfExtract) < 552
    OwnerOfExtract = OwnerOfExtract & " "
    Loop
    Worksheets("Sheet 1").Range("A10") = OwnerOfExtract
    MsgBox ("Header now equals 552 characters")
  • Re: Add spaces to a string


    incalthis,


    When you get the error and you select "DEBUG" which line of code is highlighted.


    Is the worksheet name correct. Typically unless you've renamed the sheets "Shee 1" would normally be "Sheet1"

  • Re: Add spaces to a string


    Does this work?

    Code
    Dim OwnerOfExtract As String 
     
    OwnerOfExtract = A10.Value 
     
    If Len(OwnerOfExtract) <> 450 Then 
        OwnerOfExtract  = Left(OwnerOfExtract, 71)  & Space(379) 
        MsgBox ("Header fixed") 
    End If
  • [Solved] Add spaces to a string


    Barry , Norie


    Thank you to both of you for your advice, the problem is now solved.


    Barry your code worked and you where exactly right, I had made a spelling mistake on the Sheet 1 Tab. Good Call.


    For anyone else reading this: A good way to add spaces to a string to make it a desired lenght is as follows:


    Code
    OwnerOfExtract = Worksheets("Sheet 1").Range("A10")
     
    Do While Len(OwnerOfExtract) < 552
        OwnerOfExtract = OwnerOfExtract & " "
    Loop
    Worksheets("Sheet 1").Range("A10") = OwnerOfExtract
    MsgBox ("Header now equals 552 characters")
  • Re: [Solved] Add spaces to a string


    Hi incalthis,


    Alternatively, assuming that the required length of your string doesn't change, you can just declare the string as


    Code
    Dim strMyString As String * 450


    Any values entered into that variable will be automatically padded with spaces up to the declared length of the string.


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Add spaces to a string


    Thanks Batman,


    Would the spaces be placed at the end of the characters already contained in the string? Thats what I would need to happpen


    Incalthis

  • Re: Add spaces to a string


    yes


    Code
    Sub teststringadd()
     
    Dim strMyString As String * 450
    strMyString = [a1].Value
    [a1] = strMyString
    
    
    End Sub


    This will make A1 450 chars long... x chars of existing text plus howver many "spaces" to make upto 450 chars


    Nice code Batman :)

  • Re: Add spaces to a string


    Thanks WillR,


    Works well.


    I am using it on a text file I have dropped into XL. Can you tell me why the code would stop working when the first two characters change from 12 - 19 in the last row.


    Incalthis

  • Re: Add spaces to a string


    There are 772 rows that start with 12 , row 773 starts with 19.


    Code
    Dim TheExtract As String * 552
    Dim cell     As Range
    
    
    For Each cell In Worksheets("Sheet 1").Range("A11", Worksheets("Sheet 1").Range("A65536").End(xlUp))
        TheExtract = cell.Value
        cell = TheExtract


    Thanks


    Incalthis

  • Re: Add spaces to a string


    This works fine on a test dataset


    Code
    Sub Stuff()
    Dim strExtract As String * 552
    Dim rngCell As Range
     
    For Each rngCell In Worksheets("Sheet 1").Range("A11", _
            Worksheets("Sheet 1").Range("A65536").End(xlUp)).Cells
        strExtract = rngCell.Value
        rngCell = strExtract
    Next
    End Sub


    i.e. I had rows of both 12 and 19 chars, & all ended up as 552 chars in length

  • Re: Add spaces to a string


    Sorry to be a pest Willr but I just cant seem to get this right.
    This is the entire sub, everything works except for the llenght of the last row, it has a character length of 8 and must be made up to 552. If it was always the same cell each time I run this then I could define an exact range for it, much like I did with 'OwnerOfExtract', but its not. It will change each time due to the length of the text file inserted.


  • Re: Add spaces to a string


    Your posts become more confusing each time... :(


    I now have no idea of :-
    what is wrong?
    what you are trying to do?


    Maybe you need to supply us with some sample data of how it is when you start & how you want it to be when you finish... as currently the goalposts seem to keep moving ;)

  • Re: Add spaces to a string


    Sorry Willr, I will try to explain it better.


    I place a text file into excel each week that has a top row of 71 characters and a bottom row of 8 characters. Most of the rows in between have 552 characters, your code now ensures all the 'between' rows are 552 long.


    Top row contains the name of the file owner so I called it OwnerOfExtract, as it is always in Cell A10, I called it a seperate string and your code also makes ithis 552 long.


    The bottom row contains only 8 characters and could be in any cell from A750:A1000 depending on how many rows where in the text file.


    My problem is that I cant get the bottom row to register a length of anything other than 8 characters.


    The rngCell(Offset) statements extract data and place them in the same row but a few cells to the right. They all work okay and can be ignored for this problem.


    I hope I have explained it clearly enough, if not then dont worry about it as I dont want to bog you down with this.


    Thanks
    Incalthis

  • Re: Add spaces to a string


    Hi incalthis,


    I can't see from what you have posted how is it that your final row has a string length of 8 and not 552.


    Your program converts the value of cell A10 to a string length of 552, then moves on to do the same for every cell from A11 down to the last cell in column A, which presumably includes the footer record.


    The code you have posted doesn't include the Sub .. End Sub statements; is there any other code you haven't posted?


    Alternatively, is it possible that the footer record isn't actually in column A, so it isn't being looked at by the program?


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Add spaces to a string


    Batman,


    Your assesment is correct, all cells in column A from A10 to the last one have a string length of 552 once the code is activated, as mentioned the last one only has 8.


    The footer recored is in column A and there is no more code to post.


    I have no idea why it doesnt work.

  • Re: Add spaces to a string


    I have copied your code and run it through a program of my own and I can see no reason why it wouldn't work.


    Can you try putting the following statement immediately before Next rngCell and check the results in the Immediate Pane after you have run the program:


    Code
    Debug.Print rngCell.Address, Len(rngCell.Value)


    That should tell you which cells have been actioned and what the length of each is.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!