Omen666blue
New Member
- Joined
- Aug 13, 2012
- Messages
- 39
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
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
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