Fix embedded space using VBA

[bkelly]

A data set provided me is space separated but a few cells have an intentional space in them, violating the space separation rule.

In this case, the value "00C" always found in column 2, sometimes has an embedded space.
The cell contents should have the format "00C 123456" but the import broke the contents across two cells.
When "00C" is found as the complete contents in column 2, it must be combined with a space followed by the contents of column 3, then everything from 4 to the end is moved left one column.
I cannot find the right syntax to use in the For Each statement, and in the code to select the range, within one row, from column 4 to the end and paste that range back into the row beginning with column 3.

I always try to use row, column format rather than A2 or AB3 format so please try to go that way.

Thank you for your time.

Sub fix_00C_space()
   Dim found As bool

   Dim current_row As Long  ' replace this with the current row number from the For Each statement, I don't know what that is.
 
   For Each row in this_sheet

      If (.cell(current_row, 2) = "00C") Then
        .cell(current_row, 2) = "00C " & .cell(current_row, 3)
        move_columns_4_through_end_to_the_left_by_one_column()
      End If
   Next
End Sub

 

[bobsan42]

I found errors in a version of the code you gave me and am running it for the fifth time or so, each one different.
It ran quickly on test data of about 400 rows, but takes quite a while on 300 K plus rows.
I am not good with VBA, and work at home. The error messages leave much to be desired. No one to ask but the forums.
Thank you for the code you gave me.

Fixing large amounts of data is tedious task.
My approach in such cases is to apply filter on the data to show only the rows to fix in one go. Then run adjustments only on the visible cells.
However, deletions may not work with this method.