combining cells and shifting

[Igor]

Is there a command in Excel (Office 2000) to combine the contents of
adjoining/highlighted cells and move every thing else over 1?

Sometimes when address files are parsed, as they are read from a text file
into Excel, an extra comma can throw things off by 1 (or 2). So, for
example, I would like to highlight C12 and D12, combine their contents into
C12, and then move all the other cells on row 12 to the right of D12 to the
left one cell.

If not a command, how about a macro? I recorded one but it used absolute
cell references rather than relative. Thanks for any help on this.

 

[Tom Ogilvy]

Sub ShiftLeft()
If ActiveCell.Column = 3 Then
ActiveCell.Value = Trim(ActiveCell.Value) & " " & _
Trim(ActiveCell.Offset(0, 1).Value)
ActiveCell.Offset(0, 1).Delete Shift:=xlShiftToLeft
End If
End Sub

 

[David McRitchie]

Hi Igor,
A more generic solution that works from the selection would be to use
the JOIN macro in
http://www.mvps.org/dmcritchie/excel/join.htm

Select columns C and D, run the macro, then delete Column D manually.

Instructions to install and use a macro in
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Igor]

Sub ShiftLeft()
If ActiveCell.Column = 3 Then
ActiveCell.Value = Trim(ActiveCell.Value) & " " & _
Trim(ActiveCell.Offset(0, 1).Value)
ActiveCell.Offset(0, 1).Delete Shift:=xlShiftToLeft
End If
End Sub

Thanks, but I could not get this to work consistently. Mostly, it would
only work in column C. I got it to work once in column A, but only once.
What am I doing wrong? Thanks again.

 

[Igor]

Hi Igor,
A more generic solution that works from the selection would be to use
the JOIN macro in
http://www.mvps.org/dmcritchie/excel/join.htm

Select columns C and D, run the macro, then delete Column D manually.

Instructions to install and use a macro in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---

Thanks. I looked at the links but I need to be able to work on one row at
a time and it seemed that it was designed to work with columns.

 

[David McRitchie]

You can select whatever you want.
it will join the cells per row.

You can select C2:d2
you can select c23

you can select C but you said you didn't want that.

 

[Dave Peterson]

Tom's code checked to see what column you were in:

If ActiveCell.Column = 3 Then
(column C is the 3rd column).

So if you wanted it to work in columns A:C:

If ActiveCell.Column <= 3 Then

or if you wanted it to work anywhere, you could comment out a couple of lines:

Sub ShiftLeft()
'If ActiveCell.Column = 3 Then
ActiveCell.Value = Trim(ActiveCell.Value) & " " & _
Trim(ActiveCell.Offset(0, 1).Value)
ActiveCell.Offset(0, 1).Delete Shift:=xlShiftToLeft
'End If
End Sub

(apostrophes before the If and End if statements.)

Be careful. The macro will do what it's told to do!

 

[Igor]

You can select whatever you want.
it will join the cells per row.

You can select C2:d2
you can select c23

you can select C but you said you didn't want that.

So it does. I'll also have to check out the reversing macros, too.
Thanks.

 

[Igor]

Tom's code checked to see what column you were in:

If ActiveCell.Column = 3 Then
(column C is the 3rd column).

So if you wanted it to work in columns A:C:

If ActiveCell.Column <= 3 Then

or if you wanted it to work anywhere, you could comment out a couple of lines:

Sub ShiftLeft()
'If ActiveCell.Column = 3 Then
ActiveCell.Value = Trim(ActiveCell.Value) & " " & _
Trim(ActiveCell.Offset(0, 1).Value)
ActiveCell.Offset(0, 1).Delete Shift:=xlShiftToLeft
'End If
End Sub

(apostrophes before the If and End if statements.)

Be careful. The macro will do what it's told to do!

Thanks. I consider myself warned. Also, I noticed that undo does not work,
so I better be careful.