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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,513
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
maybe try =TRIM(SUBSTITUTE(A1," "," ")), which removes leading space and any doubles be come single, only need to apply to the result
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">DANIELS  Terry                           1  5  : 2  5         0  1  : 2  6 10:01 9996</td><td style=";">DANIELS Terry 15:25 01:26 10:01 9996</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">FELTON **, Karl                              0  6  : 0  7          1  4 :19 08:12 4101 Amended</td><td style=";">FELTON **, Karl 06:07 14:19 08:12 4101 Amended</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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

Moderator
Joined
Oct 23, 2004
Messages
10,513
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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]
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,644
Messages
5,524,050
Members
409,558
Latest member
Excelinho

This Week's Hot Topics

Top