Deleting Space Based on Condition

[madhuchelliah]

Hello All, i have to delete a space based on condition in a string. I have the data in D column which is string type and range is dynamic. In all the cells in the column, if the second word(that is after first space in the string) is an alphabet (A to Z) then the space (that is first space in the string) before the alphabet has to be deleted. The alphabet will be always any single character from A to Z. it will be helpful if the output is in VBA. Thank you.

 

[DanteAmor]

You can put a couple of examples with their respective results.

 

[Scott Huish]

Sub DeleteSpaceBeforeSingleLetter()
Dim c As Range, t
For Each c In Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
t = Split(c)
If UBound(t) > 0 Then
    If Len(t(1)) = 1 And t(1) Like "[A-Z]" Then c = Replace(c, " ", "", , 1)
End If
Next
End Sub

 

[madhuchelliah]

Sub DeleteSpaceBeforeSingleLetter()
Dim c As Range, t
For Each c In Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
t = Split(c)
If UBound(t) > 0 Then
    If Len(t(1)) = 1 And t(1) Like "[A-Z]" Then c = Replace(c, " ", "", , 1)
End If
Next
End Sub

Hey Scott, it is working great. Thank you.

 

[madhuchelliah]

You can put a couple of examples with their respective results.

Hello Dan, thanks for your interest. I got the solution from Scott.