VBA: Help with my logical test to remove spaces

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

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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
maybe try =TRIM(SUBSTITUTE(A1," "," ")), which removes leading space and any doubles be come single, only need to apply to the result
 
Upvote 0
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.

Code:
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
AB
1DANIELS Terry 1 5 : 2 5 0 1 : 2 6 10:01 9996DANIELS Terry 15:25 01:26 10:01 9996
2FELTON **, Karl 0 6 : 0 7 1 4 :19 08:12 4101 AmendedFELTON **, Karl 06:07 14:19 08:12 4101 Amended
Sheet1
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
Superb, Works a treat! I chucked a quick link into my project and hey presto! I amended it slightly
Code:
[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]
 
Last edited:
Upvote 0
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)...
Code:
[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]
 
Upvote 0
Superb, Works a treat!
Good news! :)

I chucked a quick link into my project and hey presto! I amended it slightly
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top