VBA: Help with my logical test to remove spaces

[Omen666blue]

Hi, I am using a macro to pull data from a PDF sadly for some reason when I pull the lines of data random "." appear in the data.
I have a test to run through each line and format the special characters out but sadly It condenses everything down, so now instead of removing all characters I replace the "." with a Space and need a logical test to then format these spaces out.

a sample of the text I have is below:
FELTON **, Karl .............................0..6..:.0..7..........1..4.:19 08:12 4101 Amended
Above is before any editing of the raw text
Below is after removing the special characters
DANIELS Terry 1 5 : 2 5 0 1 : 2 6 10:01 9996

Now I thouhgt "aaaah Simple all i need to do is check each character and if its a space check the next character and remove it!" sadly when i reach the times 1 5 : 2 5 my logical test fails

here is a sample of my test

    For Counter = 1 To Len(Range("A" & i).value)    'do something to each character in string
    'here we'll msgbox each character
    '==== Check Characters in each string, If it is a SPACE followed by a SPACE dont copy to recalibrate===
    If Mid(Range("A" & i).value, Counter, 1) = " " And Mid(Range("A" & i).value, Counter + 1, 1) = " " Then
        recalibrate = recalibrate
        Else
    '==== If the Character is a Space perform next check ====
            If Mid(Range("A" & i).value, Counter, 1) = " " Then
    '==== If Character is a space and the last character was a number or : then dont copy the space.====
                If Mid(Range("A" & i).value, Counter - 1, 1) = "1" Or Mid(Range("A" & i).value, Counter - 1, 1) = "2" Or Mid(Range("A" & i).value, Counter - 1, 1) = "3" Or Mid(Range("A" & i).value, Counter - 1, 1) = "4" Or Mid(Range("A" & i).value, Counter - 1, 1) = "5" Or Mid(Range("A" & i).value, Counter - 1, 1) = "6" Or Mid(Range("A" & i).value, Counter - 1, 1) = "7" Or Mid(Range("A" & i).value, Counter - 1, 1) = "8" Or Mid(Range("A" & i).value, Counter - 1, 1) = "9" Or Mid(Range("A" & i).value, Counter - 1, 1) = "0" Or Mid(Range("A" & i).value, Counter - 1, 1) = ":" Then
                    recalibrate = recalibrate
                Else
                    recalibrate = recalibrate & Mid(Range("A" & i).value, Counter, 1)
                End If
            End If
    End If

to break it down i test if previous Cell is a number if it is we don't copy the space, the outcome I would like is as follows
DANIELS Terry 15:25 01:26 10:01 9996

Any Brain boxes out there with any ideas?

thanks
Chris

 

[mole999]

maybe try =TRIM(SUBSTITUTE(A1," "," ")), which removes leading space and any doubles be come single, only need to apply to the result

 

[Peter_SSs]

We haven't got a lot of examples to go on, but if you have got it to the stage where those gaps in the data are 1 or more standard spaces, then see if this is any use for tidying up the times (I'm guessing there) in the text.

Sub Tidy_Data_v1()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "( +\d)( *)(\d)( *)(:)( *)(\d)( *)(\d)"
  For i = 1 To UBound(a)
    If RX.Test(a(i, 1)) Then a(i, 1) = Application.Trim(RX.Replace(a(i, 1), "$1$3$5$7$9"))
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

My data & results. More details please if this doesn't cover it.

Book1
	A	B
1	DANIELS Terry 1 5 : 2 5 0 1 : 2 6 10:01 9996	DANIELS Terry 15:25 01:26 10:01 9996
2	FELTON **, Karl 0 6 : 0 7 1 4 :19 08:12 4101 Amended	FELTON **, Karl 06:07 14:19 08:12 4101 Amended
Sheet1

 

[Peter_SSs]

maybe try =TRIM(SUBSTITUTE(A1," "," ")), which removes leading space and any doubles be come single, only need to apply to the result

If you are suggesting using that in the worksheet, then the SUBSTITUTE is redundant since =TRIM(A1) would do the same job.

 

[mole999]

If you are suggesting using that in the worksheet, then the SUBSTITUTE is redundant since =TRIM(A1) would do the same job.

I'm sure when i typed it in, it left the spaces first time, now I have removed it I can see your point

 

[Omen666blue]

Superb, Works a treat! I chucked a quick link into my project and hey presto! I amended it slightly

[COLOR=#333333]Range("A1").Resize(UBound(a)).Value = a

so as to just simply replace the junk text that I had in the original test Cell.

Thank you![/COLOR]

 

[Rick Rothstein]

Assuming the only dots in the text are around and within the date/time part of the text, if you start with the original text in the cells (the ones with the dots in them), does this macro straighten everything out (output placed in Column B)...

[table="width: 500"]
[tr]
	[td]Sub RemoveDots()
  Dim Cell As Range
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Cell.Offset(, 1).Value = Evaluate("REPLACE(SUBSTITUTE(" & Cell.Address & ",""."",""""),FIND("":"",SUBSTITUTE(" & Cell.Address & ",""."",""""))+3,0,"" "")")
  Next
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

Superb, Works a treat!

Good news!

I chucked a quick link into my project and hey presto! I amended it slightly

Range("A1").Resize(UBound(a)).Value = a

so as to just simply replace the junk text that I had in the original test Cell.

Fair enough. Until I'm sure it is doing the right thing, I generally like to put it beside like that so that..
a) It is easy to compare the original with the new, and
b) If it doesn't work correctly, I've still got the original in place to test the next version on.