PDA

View Full Version : Solved: Delete Spaces in text but not numbers.



omnibuster
06-30-2009, 02:07 PM
:banghead: In (IE) downloading process i was using different ways for download text, but now i see that text is not same formation?
In my Sheet in columns A:A and B:B i need text same format.
(For compare)
If i use Function "RemovePunctuation" this function removed "spaces" but same time removed numbers too???
I need this numbers.
How change the function?

Private Sub CommandButton1_Click()
Dim c As Range, t As String
For Each c In Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row)
t = UCase(" " & RemovePunctuation(c.Text) & " ")
c = Trim(t)
Next
End Sub
Function RemovePunctuation(r As String) As String
With CreateObject("vbscript.regexp")
.Pattern = "[^A-Z0-100 ]"
.IgnoreCase = True
.Global = True
RemovePunctuation = .Replace(r, "")
End With
End Function

Aussiebear
06-30-2009, 03:18 PM
Try the following code (note it is untested)

Private Sub cmdButton_Click()
Dim TrimRg As Range
Dim oCell As Range
Dim func As WorksheetFunction

On Error Resume Next
Set Func = Application.WorksheetFunction
Set TrimRg = Range("A1:B" & Range ("A" & Rows.Count).End(xlUp).Row

For Each oCell in TrimRg
oCell = Func.Clean(Func.Trim(oCell))
Next
End Sub

omnibuster
07-01-2009, 01:31 AM
Thanks Aussiebear but your code delete spaces only before and after the words.

Need change function like this:
[VBA]
[Function RemovePunctuation(r As String) As String
With CreateObject("vbscript.regexp")
.Pattern = "[^A-Z0-100 ]"
.IgnoreCase = True
.Global = True
RemovePunctuation = .Replace(r, "")
.IgnoreNumbers=True
End With /VBA]

Aussiebear
07-01-2009, 02:38 AM
Ok, I found this on the www.fontstuff.com website.


Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
GoTo Test
End If
End Function

omnibuster
07-01-2009, 03:21 AM
Thanks again Aussiebear.
But if cell contains names like John Smith your function "RemoveSpaces" removed all spaces between words and result is: JohnSmith.
This is not this what i need.
And my file Sample2 second control after "RemoveSpaces says: Cell B2 format is not same as Cell A2 format??

omnibuster
07-07-2009, 02:09 AM
In the end i find: that what i need delete was not "Spaces", but "Char"-s.

Private Sub CommandButton1_Click()
Dim c As Range, t As String
'Call Tähed 'Replace: Ä to A, Õ to O, Ö to O, Ü to U
'Call Tava 'Replace: Differenc.Modific.Õ to O.

For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
t = UCase(" " & StripChar(c.Text) & "")
c = Trim(t)
Next
For Each c In Range("b1:b" & Range("A" & Rows.Count).End(xlUp).Row)
t = UCase(" " & StripChar(c.Text) & "")
c = Trim(t)
Next
End Sub
Function StripChar(s As String) As String
With CreateObject("vbscript.regexp")
.Global = True
.ignorecase = True
.Pattern = "[^\dA-Z]"
StripChar = .Replace(s, " ")
End With
End Function