Formula to move data to Left when LEFT cell is blank?

I have four cells that I use to track items. Sometimes all cells are used and sometimes only a few. I would like help in how I can automate a move in data in a cell that is RIGHT of an empty cell move to the LEFT. (i.e.) I fill cells C5, D5, E5, & F5. I then delete the data in cell D5 and want to have the data in E5 move to D5, and F5 move to E5....leaving F5 empty for the new data. kind-of like right justify. Thank you.

I don't think you can do this with a Formula, you'll be needing VBA.

Something like;

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C:G")) Is Nothing Then
    Application.ScreenUpdating = False
    
    For Each cel In Target
        myRow = cel.Row
        myCol = cel.Column
    
        For x = myCol To 7
            If Cells(myRow, myCol).Value = "" Then
                If Cells(myRow, myCol + 1).Value <> "" Then
                    Cells(myRow, myCol).Value = Cells(myRow, myCol + 1).Value
                    Cells(myRow, myCol + 1).Value = ""
                End If
            End If
        Next x
    Next cel

    Application.ScreenUpdating = True
    
End If
End Sub

Put that in the Worksheet code - It'll check columns C through G for changes, if it finds a blank cell it'll move all the values to the right along to the left.

Post back if you need more help