Combining Cell Contents - entire columns

[SV]

Greetings,
Using Excel 2003

Can the combine the contents of two columns into one column? Contents of,
say, columns C and D would become Column C with the cell contents 'merged'
into that column (So C-1 and D-1 info would now appear, together, in C-1)?

Here's what I'm trying to do:

I have a list of figures from a word-processing program that need to be
sorted. We currently enter the list into an Excel column that's been
formatted as Text because our figure numbers are 'section-figure' so it's
things like A-1, A-2.... B-17, B-18... L-5, L-6, etc.

The difficulty is, formatted as text or not, when we sort the alphanumerics,
A-10 will come before A-2, and a 12-entry list looks like this:

A-1
A-10
A-11
A-12
A-2
A-3 (etc up to A-9)

We got the idea of using one column for the 'section-' so we can put the
numbers in their own column. We can either format it as numbers or, when
sorting, be prompted to treat them as numbers. No biggie. Unfortunately,
now we have to copy those columns to paste into a word-processing program
(Paste Special > Text, even), which means there's what appears to be a large
Tab between the section number (A-) and the number (1). Now we have:
A- 1
A- 2
etc.

So I checked "Help" and found I can 'combine' cell contents (=A1&A2), and
that seems to be a great way to do it... if I want to highlight every cell,
or copy the formula and do a column-wide paste, then delete the original two
columns.

If we have to, we'll use the 'combine' function and delete the extra
columns, it's really not that difficult.

On the other hand, if there's a way to apply it to the entire columns so
that we shrink down by one, well that would be just peachy.

Any ideas?

Thanks,
Shane

 

[vezerid]

Shane,

if I understand, you problem is actually quite simple. If you presently
have two columns, one with the letters and the other with the ordinal
number, the formula:
=A2&B2
can be copied down and it will translate correctly in all subsequent
rows.

Also, one thing you might want to know, if you copy cells with
formulas, you can then paste them as values (forgetting the formula)
with Edit|Paste Special... Choose Values.

HTH
Kostis Vezerides

 

[SV]

Kostis,
Yes, that does appear to be our best option (The other being that we do a
LOT of cutting/pasting rows!).

I'd hoped there would be a less labor-intesive means. I'm going to try to
do up a macro.

Thanks for the Paste Special info, I wasn't very familiar with it and it
seems to work quite well!

Shane

 

[Gord Dibben]

Sub combine()
Dim i As Range
For Each i In Selection
i.Value = i.Value & i.Offset(0, 1).Value
Next i
End Sub

Select column C and run the macro.


Gord Dibben MS Excel MVP

 

[SV]

Gord,

Thanks for the script info. My experience in scripting was with some VERY
basic VB stuff (I was trying to figure out how to make one to change the
state of the CD drawer... if open, then close.. if closed, then open) and
manipulating an access database with Protrack... so I think I comprehend
the script:

1 Sub combine()
2 Dim i As Range
3 For Each i In Selection
4 i.Value=i.Value&i.Offset(0,1).Value
5 Next i
6 End Sub

1 Create a subroutine we're calling 'combine'
2 Dimension the variable 'i' (A cell location, I take it) as a 'Range'
variable
3 For each 'i' in the selection
4 Make the cell's value = i's value "combined with" the value of the cell
offset vertically by 0 and horizontally by 1 from cell i
5 Next i (Go down a cell and repeat the step until there are no more)
6 End the routine

So, I think I comprehend the subroutine....

Do I just open up the Scripting editor (suspiciusly reminiscent of my
Protrack II and VB stuff) and plug it in and it'll create a macro called
"combine" for me? I approach things like this VERY cautiously!

Also, would the following insert a dash (so I don't have to type them in
ever letter):

Sub combine()
Dim i As Range
For Each i In Selection
i.Value=i.Value&"-"&i.Offset(0,1).Value
Next i
End Sub


Shane

 

[SV]

Gord,
Hey, I got real bold and brave and stuff (Hey, what could I destroy... one
almost-empty workbook?) and went to the Macro editor and just plugged it in.
It WORKS! THANKS!!!

I even went so far as to chagne line 4 to
i.Value=i.Value&"-"&i.Offset(0,1).Value

This way the column now has a dash between the letter and number.

Feeling SUPER bold, I added
i.Offset(0,1).Value=""
This deleted the right-hand column information and, again, WORKED!!

Thanks! I really appreciate that. I knew Office products and VB worked
together, but never had a reason do to it.

Shane

 

[Gord Dibben]

With your workbook open hit ALT + F11 to open the Visual Basic Editor.

CTRL + r to open Project Explorer

Select you workbook/project and right-click>insert module.

Copy and paste the code into that module.

CTRL + q to return to the workbook.

Yes, the code you edited will give you the "-" between the words.

For more help on VBA and Macros see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Gord

 

[Gord Dibben]

OK

Good onya.

I posted to your other response before reading this but you can probably ignore
that now you're up and flying<g>


Gord