Take the 2-minute tour ×

I would like to do something like the following using worksheet functions.

If cell (A1 = "ABC"), 
       then append to the begin of the cell B1 "ABC:" 
       delete cell A1 and cell B1 becames A1, C1 -> B1 and so on

       append "" (or do nothing) - because in cell B2 I have already information

Is this possible? Or do I need VBA?

share|improve this question

1 Answer 1

Apparently, you can't do this in excel using the the simple if statement. You need to do this in VBA:

Public Sub StergeSTorOR()
Dim oRow As Range
Dim cell As Range
Dim i As Long, j As Long

    Application.ScreenUpdating = False

    For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1

        For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1

            If Cells(i, j).Value = "AB:" Or Cells(i, j).Value = "CD:" Then
                Cells(i, j + 1).Value = Cells(i, j).Value & Cells(i, j + 1).Value
                Cells(i, j).Delete shift:=xlShiftToLeft
                End If
        Next j
    Next i

    Application.ScreenUpdating = True
End Sub
share|improve this answer

Your Answer


