Excel – Insert text to the beginning of every Cell in a Column

google-spreadsheetsmicrosoft excel

I'm trying to find a way to insert text in to every Cell in a specific Column.

e.g. Column C has 50 rows: I need to insert 0049 to the beginning of every Cell.

I didn't had any luck finding/creating a formula for this.

Help is much appreciated.

Best Answer

Create a blank Column (D) and use "CONCATENATE":

=CONCATENATE("0049",C1)

Drag that down for each adjacent cell in Column C and it will add 0049 as a prefix.

Related Solutions

Excel: set default formula for each cell in column

Here is a small demo that you can adapt to your actual schema. In the demo:

the column of interest is column B
if more than one row is inserted, nothing will happen
if a row below the data area is inserted, nothing happens
if a row is deleted, nothing happens
if a row in the middle of the data is inserted, the cell in column B will be filled with either the formula above or the formula below
if there is no formula above or below the new cell, nothing happens

Insert the following sub in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, r1 As Range, r2 As Range

    Set r = Target
    If r.Rows.Count > 1 Then Exit Sub
    If r.Columns.Count <> Cells.Columns.Count Then Exit Sub
    If r.Row = 1 Then Exit Sub

    Set r1 = Intersect(r, Columns(2))
    If r1.Value <> "" Then Exit Sub

    If r1.Offset(-1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(-1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

    If r1.Offset(1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

End Sub

Best Answer

Related Solutions

Excel: set default formula for each cell in column

Related Question