Excel – Adding a space to the beginning of every cell in excel

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.

I would recommmend preprocessing the data before importing them into Excel.

However I created procedure in VBA that does what you want:

To install the macro

Open Excel -> Alt+F11 -> Insert -> Module -> paste the following code -> Ctrl+S -> and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the drop-down list

Sub ToManyColumns()
    Dim firstCellRow As Long
    firstCellRow = 1              'change this if you don't want to start at A1
    Dim firstCellColumn As Long
    firstCellColumn = 1           'change this if you don't want to start at A1
    
    Application.ScreenUpdating = False
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    Dim column As Long
    column = firstCellColumn
    Dim startIndex As Long
    Dim endIndex As Long
    Dim lastRow As Long
    lastRow = firstCellRow
    
    Do While True
        'find the range to copy
        startIndex = ActiveCell.row
        Do While ActiveCell.Value <> ""
            endIndex = ActiveCell.row
            ActiveCell.Offset(1).Activate
        Loop
        
        lastRow = ActiveCell.row
        
        Range(Cells(startIndex, firstCellColumn), Cells(endIndex, firstCellColumn)).Select
        Selection.Copy
        Cells(firstCellRow, column).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        
        'get back to last rowIndex
        Cells(lastRow, firstCellColumn).Activate
        ActiveCell.Offset(1).Activate
        
        If ActiveCell.Value = "" Then Exit Do
        
        column = column + 1
    Loop
    
    'cleanUp -------------------------------------------
    Dim deleteFrom As Long
    Dim deleteTo As Long
    deleteTo = ActiveCell.row
    
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    Do While ActiveCell.Value <> ""
        ActiveCell.Offset(1).Activate
    Loop
    deleteFrom = ActiveCell.row
    
    Range(Cells(deleteFrom, firstCellColumn), Cells(deleteTo, firstCellColumn)).Select
    Selection.ClearContents
    
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    'cleanUp -------------------------------------------
    
    Application.ScreenUpdating = True
End Sub

To run the macro:

1. Make sure that you're in the worksheet you want to be! And click anywhere into the worksheet! (Because this macro runs in the Activated Worksheet)
2. Alt+F11 -> Click anywhere into the code -> press F5

You can also create a button in the worksheet and assign the macro to it - it's more user-friendly and you don't have to check that you're in the right worksheet.

Notes

If you don't want the macro to start at cell A1 (for example: start at different column), then change the numbers on 3rd and 5th lines.

For this purpose it's better to preprocess the data than use macros...