#### Omen666blue

##### New Member

- Joined
- Aug 13, 2012

- Messages
- 39

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

Code:

```
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