Add one space in string

[Westbury]

Hi,

I'm trying to add one space in a series of file names which are in my col A.

First problem is how do I insert a test so that the inserted space will only apply to file names which are 13 or 14 characters long? How would I add another check to overwrite any files where the 5th cahacter is a "-"?
My code doesn't work as I get a runtime error on the line where I'm trying to insert the space.

Help appreaciated!

Sub Add_one_space()

    Dim i, x As Integer
    Dim LastRow As Long
            
    Sheets("FC plans").Select
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

        For x = 2 To LastRow Step 1
                        
               If Len("A" & x) < 13 Or Len("A" & x) > 14 Then
                    
               'how do I get to the next x from here?
                    
               End If
                
        Range("A" & x) = Left(Range("A" & x), 4) & " " & Mid(Range("A" & x), 5, Len(Range("A" & x) - 4))        
                       
        Next x
    
End Sub

 

[GWteB]

How about ...

Sub Westbury()

    Dim rng As Range, c As Range

    With Sheets("FC plans")
        Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
    End With

    For Each c In rng
        If Len(c) < 13 Or Len(c) > 14 Then
            'do nothing
        Else
            If InStr(c, "-") = 5 Then
                c = Left(c, 4) & " " & Right(c, Len(c) - 5)
            End If
        End If
    Next c
End Sub

 

[Rick Rothstein]

Here is another macro that should work...

Sub Westbury()
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "????-????????" Or Data(R, 1) Like "????-?????????" Then Mid(Data(R, 1), 5) = " "
  Next
  Range("A1").Resize(UBound(Data)) = Data
End Sub

 

[Fluff]

@Rick Rothstein
Not sure if I'm missing something, but it looks as though you have and extra Data(R, 1) Like in there.

 

[Rick Rothstein]

@Rick Rothstein
Not sure if I'm missing something, but it looks as though you have and extra Data(R, 1) Like in there.

Yep, not sure what I did but I corrected it in the original message. Thanks for noting it.

 

[Fluff]

 

[Peter_SSs]

.. or possibly a non-looping approach?

Sub InsertSpace()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",if(abs(len(#)-13.5)<1,if(mid(#,5,1)=""-"",replace(#,5,1,"" ""),#),#))", "#", .Address))
  End With
End Sub

 

[Westbury]

Rick, GWteB, Peter, Many thanks for your responses. Diplomatically I have to say that only Rick's solution works !

GWteB & Peter, there's no response to the code.

Rick, how can the code be modified to accommodate other longer file names? If the space is always inserted after the 4th character and the remaining lenght is unconstrained.

 

[Rick Rothstein]

Assuming by "...the space is always inserted after the 4th chraracter..." that you meant what you originally asked for, namely, that we are replacing a dash when one exists in the 5th position and doing nothing otherwise...

Sub Westbury()
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "????-*" Then Mid(Data(R, 1), 5) = " "
  Next
  Range("A1").Resize(UBound(Data)) = Data
End Sub

 

[Westbury]

Inserting a space after the 4th character in any file name or replacing the - with a space would be a comprehensive solution.