Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a variable My_Var dimensioned as strng.
    The var can assume value, similar "sal vatore" or "s vatore"....
    I want replace the number of blank space in internal of string with only one balnk space, how to?
    Note:
    the number of balnk space are variable.

    Final result in my case:

    sal vatore
    s vatore

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can use:
    Code:
    My_Var = Application.Trim(My_Var)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    You can use:
    Code:
    My_Var = Application.Trim(My_Var)
    ... but if i am not wrong, the line code delete all blank space, or not?
    In effect i want to reduce the number of blank space in one only.
    example:
    if the string contain 4 balnk space reduce the current number of blank space in one blank space
    if the string contain 9 balnk space reduce the current number of blank space in one blank space

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    towcester, england, UK
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Trim will remove any Leading or trailing blanks from the string, but if you have blanks within the string text itself such as fred****smith (where *represents a blank in this case) then Trim will not be suitable... particularlu if you want 1 blank to remain.

    in such a case you need to detect 2 blanks together and keep looping the string until they have all been swapped for a single blank.

    something link this should (hopefully) suit:



    Function TrimInternal(strStringToCheck As String) As String


    Dim strTemp As String
    strTemp = Trim(strStringToCheck)
    Do While InStr(strTemp, " ") > 0
    strTemp = Left(strTemp, InStr(strTemp, " ") - 1) & Mid(strTemp, InStr(strTemp, " ") + 1)
    Loop
    TrimInternal = strTemp

    End Function


    so you're calling code would be something like:
    ActiveSheet.Range("B1").Value = TrimInternal(ActiveSheet.Range("A1").Value)


    Hope this helps

    Cheers
    Phil

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Did you try it?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by philadams-uk View Post
    Trim will remove any Leading or trailing blanks from the string, but if you have blanks within the string text itself such as fred****smith (where *represents a blank in this case) then Trim will not be suitable... particularlu if you want 1 blank to remain.
    The trim function will remove the leading, trailing and multiple spaces within the text - there is no need to create a function.


    Select the text in the A column and click the button.

    The macro
    Code:
    Sub trimmer()
    Dim rRng As Range
    For Each rRng In Selection
    	rRng = Application.Trim(rRng)
    Next
    End Sub
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [s]Phil,
    Your function, as posted, results in all spaces being removed.
    This line
    Do While InStr(strTemp, " ") > 0 should be Do While InStr(strTemp, " ") > 0. It was checking for the existence of a single space instead of multiple spaces.[/s]


    An alternate method would be to use the Replace function (if you really want to re-invent the trim)
    Code:
    Function trimIt(myCell As String) As String
    Do While InStr(myCell, " ") > 0
    	myCell = Replace(myCell, "__", " ")
    Loop
    trimIt = myCell
    End Function
    Double underscore being used to represent two spaces because post is being Trimmed to remove extra spaces within the text.

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My apologies Phil. I bet you had two spaces in the line to begin with and the lounge software was "kind enough" to trim out the extra spaces when it parsed the post. I see that all of my double spaces are now single.

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    towcester, england, UK
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    Phil,
    Your function, as posted, results in all spaces being removed.

    sorry for some reason i'm missing a blank between the quotes in my fragment - each of the function calls 'InStr(strTemp, " ")' should have 2 blanks between the quotes, not 1..... if there's only 1, all spaces will be removed.

    :-/

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by philadams-uk View Post
    sorry for some reason i'm missing a blank between the quotes in my fragment - each of the function calls 'InStr(strTemp, " ")' should have 2 blanks between the quotes, not 1..... if there's only 1, all spaces will be removed.

    :-/
    Tks to thr all...!!!
    Resolaved with one or more solution in this post.

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    towcester, england, UK
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Did you try it?
    sorry Rory - hadn't picked up on the subtle difference in behaviour the worksheet function vs the VBA function. That'll teach me.... :-)

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by philadams-uk View Post
    sorry Rory - hadn't picked up on the subtle difference in behaviour the worksheet function vs the VBA function. That'll teach me.... :-)
    No worries. It can make life interesting on occasion...
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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