Shifting Cells

[jislandhopper]

Hi,
I’m using Excel 2007 and I have some data which has been imported from an old system and the data copies over all in one cell. I can separate the data into 3 columns by using the text to columns, which helps because an option is to make a break after every space. But because the data I’m copying isn’t complete the data is not in the right columns, I’ve tried to sort and just shift them over but it can’t be performed over multiple selections.
Is it possible to make an argument if column C is blank move cell A & B across one cell? Below is an example of the data before, and what I would like it to see.

Whats happening after using text to columns
column A	column B	column C
CUSTOMER REF	OUR REF	SERVIVCE
REF1	70445	LON1
REF2	70446	LON1
70447	BIR1
70448	BIR2
What I would like it to look like
column A	column B	column C
CUSTOMER REF	OUR REF	SERVIVCE
REF1	70445	LON1
REF2	70446	LON1
	70447	BIR1
	70448	BIR2

<tbody>
</tbody>

I would really appreciate any assistance
Thanks,
Jason.

 

[Teeroy]

Hi Jason,

To affect the cell position you'll need to use VBA. Try the following VBA code.

Sub test()
Dim lr As Long, Rng
With ActiveSheet.UsedRange
    lr = .Cells(.Rows.Count, 1).Row
End With
On Error GoTo safeExit
For Each Rng In Range("C1:C" & lr).SpecialCells(xlCellTypeBlanks).Areas
    Rng.Offset(0, -2).Insert shift:=xlShiftToRight
Next
safeExit: Exit Sub
End Sub