visual basic and "OR" command

It worked perfectly. Thank you very much and congratulations!!

Hi Juan,

There is another type of spaces called non-breaking space, and you need to a special trick to get rid of it.

I think this space is what you have in your worksheet.

You can represent this space using this function:

Chr(160)

And this one for the regular space:

Chr(32)

To remove this space in addition to the regular space, please try this code:

Sub RemoveAllSpacesTypes()

    Columns("C:C").Select
    
    Dim spaces As Variant
    spaces = Array(Chr(32), Chr(160))
    
    For Each s In spaces
    
        Selection.Replace What:=s, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Next s

End Sub

Please note that this code will remove all spaces including the spaces in between words.

Regards

Good morning Haytam and thank you very much as it worked again!

Do you know  why when I try to delete in a column blank spaces Excel does not find any blank spaces? The column has text but it is under the general format. Even if I change the format to text cells, it does not work...

Columns("C:C").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Thank you very much in advance, Juan

Yes, that possible!

But you cannot record them, you have to craft them as follows:

Sub TextManipulationProcess()
        
On Error Resume Next
    
    Dim filledCells As Integer
    filledCells = Application.WorksheetFunction.CountA(Selection)
    
    Dim cell As Range
    For Each cell In Selection
    
        If Mid(cell, 3, 1) = ":" Then
            'Do nothing
        ElseIf Mid(cell, 3, 1) = " " Then
            cell = Application.WorksheetFunction.Replace(cell, 3, 1, ":")
        ElseIf Mid(cell, 3, 1) <> " " And Mid(cell, 3, 1) <> ":" And cell <> "" Then
            cell = Application.WorksheetFunction.Replace(cell, 3, 0, ":")
        Else
            cell = ""
        End If
        
        Dim counter As Integer
        counter = counter + 1
        
        If counter >= filledCells Then
            Exit For
        End If
    
    Next cell

    Dim r As String
    r = Selection.Range("A1").Address(False, False)
    If Range(r).Value = "" Then Exit Sub
    
    Selection.TextToColumns Destination:=Range(r), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=".", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
    2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2)), TrailingMinusNumbers:=True

On Error GoTo 0

End Sub

Please note that you have to select the column before you run this code.

Hope that helps

Thank you again. it works!

However, would it be possible to run those instructions from a macro command?

If so, before you apply the Text to Column process, you have to make the data consistent somehow.

Supposing that these text times always at the beginning of the string, and the third character of the string should always be ( : ), I would suggest this formula:

=IF(MID(A1,3,1)=":",A1,
IF(MID(A1,3,1)=" ",REPLACE(A1,3,1,":"),
IF(AND(MID(A1,3,1)<>" ",MID(A1,3,1)<>":",A1<>""),REPLACE(A1,3,0,":"),"")))

After that, https://support.office.com/en-us/article/Replace-a-formula-with-its-result-38001951-c0e8-4fbd-9048-37ef81e2891e, and then apply the text to columns process.

Hope that helps

Hi Haytam, good morning and thank you very much for your clear and quick answer.

I have realized that the problem comes from imported data.

Data is not always perfectly written. For instance, "16:40" some times comes as "16 40" or "1640". 

So, when executing "text to column", cells having having the "16:40" form are considered as a personalysed form. On the other hand cells having ""16 40" or "1640" are considered as a general form. 

If I use your proposed solution and convert everything into text, before changing "16 40" or "1640"  into "16:40" it would be very difficult to correct the wrong cells. Is there anyway to deal  with this problem?

Thank you very much again