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

 

[Rick Rothstein]

Those two options would produce noticeably different results. In any case, did the code I posted in Message #9 work for you?

 

[Westbury]

On close examination your code in message #9 is replacing the 5th character with a space rather than inserting a space between the 4th and 5th.

I'd be grateful if you could modifiy the code.
Thanks.

 

[Westbury]

I persevered with Rick's earlier proposal and some further editting of my own and came up with

Sub Westbury()
    Dim rng As Range, c As Range, str As String

    With ActiveSheet
        Set rng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
    End With

        For Each c In rng
    
            If Not Left(c, 3) = "IFR" Then   'the first 3 characters of some file names
                If Len(c) = 12 Then
                c = Left(c, 4) & " " & Mid(c, 5)   ' the next 3 ifs to accommodate my file names which are generally AAAABBB11.* 
                ElseIf Len(c) = 13 Then
                c = Left(c, 4) & " " & Mid(c, 6)
                ElseIf Len(c) >= 14 Then
                c = Left(c, 4) & " " & Mid(c, 6)
                End If
            End If
            
        Next c
    
End Sub

 

[Rick Rothstein]

Sorry for not responding sooner but personal commitments kept me busy offline. If I am not mistaken, the following more compact code will do what your last posted code does...

Sub Westbury()
    Dim C As Range

    For Each C In Range("A2", Cells(.Rows.Count, "A").End(xlUp))

        If Not Left(C, 3) = "IFR" And Len(C) > 11 Then C = Application.Replace(C, 5, 0, " ")
    
    Next C
    
End Sub

 

[Westbury]

Thanks for following up on this. I now have 2 solutions and, yes, yuors is more compact.