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

Hi Juan,

I've tested this code, but ended up with this result:

Please note that all of the times are in Time format, even if the AM/PM isn't displayed, where it's visible in the formula bar!

I think the reason why you got some cells formatted in AM/PM is that these cells have a preset custom format to be formatted that way!

To keep all the results in Text format, where all cells are aligned to the left by default, just change the second argument for each Array function from 1 to 2 as follows:

Before:

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

After:

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), 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

Or you can change the format option to Text during recording the macro as below screenshot:

Regards

Hello again Haytam,

I have recorded some new commands within the macro where you have already helped me.

Enclose please find it:

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

The problem is that the original text:

10:40.XX.bbb.ccc
11:25.ddd.eee.fff
11:33.ggg.hhh.iii
11:42.jjj.kkk.lll
12:10.ppp.qqq

after running the macro, sometimes changes the first text into a time form and others not.

For instance:

10:40

11:25:00:AM

11:42

12:10:00:PM

And i would like to keep the text form.

Can you help me again?