Results 1 to 28 of 28

Thread: [RESOLVED] Remove blank space on the first line of text (text box)

  1. #1

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Resolved [RESOLVED] Remove blank space on the first line of text (text box)

    Hi Guys and mods,

    Sorry for asking another help.

    Been trying to code how to remove the extra line on the first part of text to no avail.

    I tried the one below but it also removes the space between words.

    Code:
    Text1.Value = Replace(Text1.Value, " ", "")
    I'm an idiot for using that simple code into a complex issue. lol.

    Btw, below is the picture of the issue that i'm trying to resolve (removing blank space on the first part of the text inside the textbox). Thanks!!

    Name:  textbox.bmp
Views: 611
Size:  280.4 KB

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Remove blank space on the first line of text (text box)

    Split() the .Text to a string array, loop through all items in the array and use Trim() on each item, reassign the array using Join() to the .Text property again

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Remove blank space on the first line of text (text box)

    How did they get there in the first place? In other words can't you do it before loading the text box?
    Please remember next time...elections matter!

  4. #4
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: Remove blank space on the first line of text (text box)

    To remove a single space from the start of each line:

    Code:
    sBuffer = vbCrLf & sText 
    sText = Replace( vbCrLf & sText, vbCrLf & " ", vbCrLf ) )
    sText = Mid$( sBuffer, 1 + Len( vbCrLf ) )
    If you need to get of multiple spaces or other characters (vbTab, perhaps), change the blue bit.

    Regards, Phill W.

  5. #5
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Remove blank space on the first line of text (text box)

    As you have a 'list' showing in this multiline textbox, why not use a listbox instead...using LTrim() or Trim() would solve your problem easily enough.

  6. #6

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by Arnoutdv View Post
    Split() the .Text to a string array, loop through all items in the array and use Trim() on each item, reassign the array using Join() to the .Text property again
    Sorry but I'm not that good in VB especially on arrays and I don't know where to start based on the instructions you provided. Thanks for your time.

    Quote Originally Posted by TysonLPrice View Post
    How did they get there in the first place? In other words can't you do it before loading the text box?
    It's from another source and just pasted there with spaces.

    Quote Originally Posted by Phill.W View Post
    To remove a single space from the start of each line:

    Code:
    sBuffer = vbCrLf & sText 
    sText = Replace( vbCrLf & sText, vbCrLf & " ", vbCrLf ) )
    sText = Mid$( sBuffer, 1 + Len( vbCrLf ) )
    If you need to get of multiple spaces or other characters (vbTab, perhaps), change the blue bit.

    Regards, Phill W.
    Thanks, Phill! Tried your code but I'm getting a sytax error. Tried removing one ) at the ned of the 2nd line code but nothign happens. Replaced stext to tex1.text and it erased everything. Thank you very much for your time. hope you can figure out something to help me.

    Quote Originally Posted by SamOscarBrown View Post
    As you have a 'list' showing in this multiline textbox, why not use a listbox instead...using LTrim() or Trim() would solve your problem easily enough.
    It's needed to be on textbox as there are a lot of data and I there's another function to remove duplicates on that box. The problem is that, some duplicate words or text is not removed when they have different spaces at the start of text.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Remove blank space on the first line of text (text box)

    from your other thread, change one line
    Code:
    For i = 0 To UBound(a)
        col.Add trim(a(i)), trim(a(i))
    Next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by westconn1 View Post
    from your other thread, change one line
    Code:
    For i = 0 To UBound(a)
        col.Add trim(a(i)), trim(a(i))
    Next
    Thanks, westconn! I can say that it's working fine but ONLY if the text is manually written on the textbox. Yes it removes the duplicate text and spaces before the line but if the data is copied over from a cell like the picture shown below. It does nothing.

    Name:  textbox.bmp
Views: 269
Size:  325.4 KB

  9. #9
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by a-a06 View Post
    Sorry but I'm not that good in VB especially on arrays and I don't know where to start based on the instructions you provided. Thanks for your time.
    Top of head, untested...

    Code:
    Dim aText() As String, i As Long
    
    If Len(Text1.Text) > 0 Then
      If Instr(1, Text1.Text, vbCrLf) > 0 Then
        aText = Split(Text1.Text, vbCrLf)
      Else
        aText = Split(Text1.Text, vbLf)
      End If
      For i = 0 To UBound(aText)
        aText(i) = Trim$(aText(i))
      Next i
      Text1.Text = Join(aText, vbCrLf)
    End If

  10. #10
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: Remove blank space on the first line of text (text box)

    > "Tried your code but I'm getting a sytax error. Tried removing one ) at the ned of the 2nd line code but nothign happens."

    Well spotted.

    Code that you get from forums like these is unlikely to be a finished, working product.
    You will have to try and understand what the code does and "adapt" it to fit you needs.

    > "Replaced stext to tex1.text and it erased everything."

    That's probably because there's a wrong variable name in my code (apologies), which you would have spotted if you had the "Require Variable Declaration" option turned on.
    Please do so. It will save you hours and hours (and hours) of debugging.

    Let's try that again:

    Code:
    ' Grab the text, plus an extra, leading line break. 
    sBuffer = vbCrLf & Text1.Text 
    
    ' Replace a single psace at the start of each line. 
    sBuffer = Replace( sBuffer, vbCrLf & " ", vbCrLf ) )
    
    ' Put the text back, removing that extra, leading line break. 
    Text1.Text = Mid$( sBuffer, 1 + Len( vbCrLf ) )
    Regards, Phill W.

  11. #11

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by Arnoutdv View Post
    Top of head, untested...

    Code:
    Dim aText() As String, i As Long
    
    If Len(Text1.Text) > 0 Then
      If Instr(1, Text1.Text, vbCrLf) > 0 Then
        aText = Split(Text1.Text, vbCrLf)
      Else
        aText = Split(Text1.Text, vbLf)
      End If
      For i = 0 To UBound(aText)
        aText(i) = Trim$(aText(i))
      Next i
      Text1.Text = Join(aText, vbCrLf)
    End If

    Thanks, Arnoutdv! The code works fine if data is manually inputted on textbox but does not work if data is copied from a cell.

    Quote Originally Posted by Phill.W View Post
    > "Tried your code but I'm getting a sytax error. Tried removing one ) at the ned of the 2nd line code but nothign happens."

    Well spotted.

    Code that you get from forums like these is unlikely to be a finished, working product.
    You will have to try and understand what the code does and "adapt" it to fit you needs.

    > "Replaced stext to tex1.text and it erased everything."

    That's probably because there's a wrong variable name in my code (apologies), which you would have spotted if you had the "Require Variable Declaration" option turned on.
    Please do so. It will save you hours and hours (and hours) of debugging.

    Let's try that again:

    Code:
    ' Grab the text, plus an extra, leading line break. 
    sBuffer = vbCrLf & Text1.Text 
    
    ' Replace a single psace at the start of each line. 
    sBuffer = Replace( sBuffer, vbCrLf & " ", vbCrLf ) )
    
    ' Put the text back, removing that extra, leading line break. 
    Text1.Text = Mid$( sBuffer, 1 + Len( vbCrLf ) )
    Regards, Phill W.

    Thanks, Phil and apologies as I'm still trying to learn this kind of stuff. Please don't scold me. Your code works too, but same as the above, it does not remove the space from copied cells.

    I'm guessing that the issue here is that the data from copied cells is actually a straight one and was just wrapped that's why it displayes like that when it's pasted on the textbox.

  12. #12
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Remove blank space on the first line of text (text box)

    What do you mean with "copied cells"?
    What use case are you describing?

    The code from both Phil and myself can be called at any time you like.

  13. #13

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    The picture below shows where I get the data. It's actually a straight text and was just wrapped to display it separately on the next line of the cell.

    If the data are manually written on the textbox with space on it before the text, codes works fine. If data is from the cell on the picture below, nothing happens.



    Name:  textbox.bmp
Views: 252
Size:  325.4 KB

  14. #14
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: Remove blank space on the first line of text (text box)

    That suggests that either that the spacing character is something other than a space or the word-wrapping is giving you the "illusion" of lines - not sure what you can do about the latter.

    Are those "Query Result" cells "assembled" from something else (other cells in the workbook)?

    If so, can you go back to the source [cells] for this data and use those instead of this, bolted-together, version of it (because this is difficult to work with and the "raw data" is often easier).

    Regards, Phill W.

  15. #15

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    I figured that out too. The data is from the sharepoint site.

    It's actually like this: apple shake mango shake chocolate cake

    and I just wrapped the text to make it more readable by putting other text on the next line. I'll try to figure out how to arrange the raw data. THanks for all the help

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Remove blank space on the first line of text (text box)

    you can test using vbCr and vbLf for splitting, inplace of the vbnewline or vbcrlf (which are effectively the same)
    one of those is likely to be the line wrap

    It's actually like this: apple shake mango shake chocolate cake
    copy an exact string as downloaded into a post
    or
    post sample of the workbook with data in textbox (zip first)

    as this all appears to be in excel, should be in office development
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Remove blank space on the first line of text (text box)

    Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

  18. #18

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Below is a sample string of text. I already managed to removed that quotation marks. It is displayed like that becasue the test is wrapped but literally, it's just one straight line.

    "Mango Pie
    *Apple Pie
    *Banana Cake"
    "Mango Pie
    *Apple Pie
    *Mango Banana"
    "Mango Pie
    *Apple Shake
    *Milk Shake"

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Remove blank space on the first line of text (text box)

    the lines are separated by linefeed, which is chr(10), same as vblf

    change the split function to use vblf
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  20. #20

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by westconn1 View Post
    the lines are separated by linefeed, which is chr(10), same as vblf

    change the split function to use vblf
    Thanks, westconn. Tried that one and here goes the code:

    Code:
    Private Sub CommandButton4_Click()
    Dim col As Collection
    Set col = New Collection
    s = Text1
    a = Split(s, vbLf)
    On Error Resume Next
    For i = 0 To UBound(a)
         col.Add Trim(a(i)), Trim(a(i))
    Next
    For Each wd In col
        res = res & vbNewLine & wd
    Next
    Text1 = Trim(res)
    End Sub
    and below is the result. some words are not separated. thank you really westconn. appreciate your time here.

    Mango PieApple PieBanana CakeMango BananaApple ShakeMilk Shake

  21. #21
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Remove blank space on the first line of text (text box)

    res = res & vbNewLine & wd
    this should insert a carriage return /line feed between each string, which are not present in the string posted above, though apparently was working before

    try putting a vbtab or vblf in place of vbnewline
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  22. #22

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by westconn1 View Post
    this should insert a carriage return /line feed between each string, which are not present in the string posted above, though apparently was working before

    try putting a vbtab or vblf in place of vbnewline
    Using vbtab just pushes the text to the right while vblf does nothing.

    I'm not sure why the textbox detects it like that.

  23. #23
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Remove blank space on the first line of text (text box)

    did you have multiple lines in the text before?

    post a sample workbook that demonstrates the problem
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  24. #24

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by westconn1 View Post
    did you have multiple lines in the text before?

    post a sample workbook that demonstrates the problem
    There's just 1 line. In a cell, I used alt+enter to go to the next line on the same cell that's why it's like that.

    Attached is a sample workbook. thanks

    Sample.zip

  25. #25
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Remove blank space on the first line of text (text box)

    there were some hidden anomalies in the workbook, that would not be seen in the forum, without the sample workbook

    there are hidden characters (Chr(160)) on each line after the first, not spaces (Chr(32))
    try this code
    Code:
    Dim col As Collection
    Set col = New Collection
    s = Range("b11")
    s = Replace(s, Chr(160), "")
    a = Split(s, Chr(10))
    On Error Resume Next
    For i = 0 To UBound(a)
        
        col.Add Trim(a(i)), Trim(a(i))
    Next
    res = ""
    For Each wd In col
        res = res & wd & vbLf
    Next
    res = Left(res, Len(res) - 1)
    Debug.Print res
    i do not really think it makes any difference if you join the string with vblf or vbcrlf (or any equivalents), the output appears to be the same
    if debug. print produces a muti line output and the textbox does not then i am not sure what the problem is and would have to test on a later version of office (i may do this tomorrow), as this version does not like textboxes on worksheets from later versions
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  26. #26

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    westconn, the idea of Chr(160) resolved the issue.

    I did not change any codes on the one you have provided to remove duplicates. Instead, I added one more line above the code.
    to remove that chr(160)
    Code:
    Text1.Value = Replace(Text1.Value, Chr(160), "")
    Thank you very much for your help! I'll mark this resolve tomorrow coz I'm not sure if you want to add something.

  27. #27
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Remove blank space on the first line of text (text box)

    The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  .
    i had not struck chr(160) before, so i looked it up
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  28. #28

    Thread Starter
    Addicted Member a-a06's Avatar
    Join Date
    Dec 2007
    Location
    P.H.
    Posts
    147

    Re: Remove blank space on the first line of text (text box)

    Quote Originally Posted by westconn1 View Post
    i had not struck chr(160) before, so i looked it up
    Well that did the trick. It was very informative on my end as I have no idea with such variables. Thank you again westconn.

    Life saver!

Posting Permissions

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



Click Here to Expand Forum to Full Width