replace blank space in a string

Topic

New Reply

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

Reply | Quote

Replies

You can use:

Code:

My_Var = Application.Trim(My_Var)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Did you try it?

Reply | Quote

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….

Reply | Quote

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…

Reply | Quote

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.

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.

Reply | Quote

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.

:-/

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote