Merge Cell text contents possible?

[Guest]

Is it possible to merge the contents of adjacent cells, e.g., so that you
retain the text from both cells?

I often need to develop a keyword field and I need to add the city, country,
and title, into a single column.

Thanks
Jeff
~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.com

 

[JE McGimpsey]

Is it possible to merge the contents of adjacent cells, e.g., so that you
retain the text from both cells?

I often need to develop a keyword field and I need to add the city, country,
and title, into a single column.

Take a look at

http://www.mcgimpsey.com/excel/mergedata.html

 

[Guest]

Thanks again!
Jeff

Take a look at

http://www.mcgimpsey.com/excel/mergedata.html

~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.com

 

[Guest]

This is my first macro attempt in Excel with pasting code, I'm going to have
to do some reading. Can't figure out how to get the wrapper or the actual
macro to work after pasting into module.
The format seems to have changed since I last fooled with it in Office X. or
2001. I usually record macros by doing them. Now I see multiple modules but
can't seem to get them to play nice together.

Take a look at

http://www.mcgimpsey.com/excel/mergedata.html

~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.com

 

[JE McGimpsey]

This is my first macro attempt in Excel with pasting code, I'm going to have
to do some reading. Can't figure out how to get the wrapper or the actual
macro to work after pasting into module.
The format seems to have changed since I last fooled with it in Office X. or
2001. I usually record macros by doing them. Now I see multiple modules but
can't seem to get them to play nice together.

Try this variation:

Public Sub ColumnsToText()
'J.E. McGimpsey http://www.mcgimpsey.com/excel/mergedata.html
Const sDELIM As String = " "
Dim vTxtArr As Variant
Dim rRng As Range
Dim nTop As Long
Dim i As Long
Dim j As Integer
Set rRng = Intersect(Selection, Selection.Parent.UsedRange)
vTxtArr = rRng.Value
nTop = UBound(vTxtArr, 1)
For i = 1 To nTop
For j = 2 To UBound(vTxtArr, 2)
vTxtArr(i, 1) = vTxtArr(i, 1) & sDELIM & vTxtArr(i, j)
Next j
Next i
ReDim Preserve vTxtArr(1 To nTop, 1 To 1)
rRng.Resize(, 1).Value = vTxtArr
End Sub

Change the delimiter as desired. This macro will work on the cells you
have selected when you call it.

Put this in a regular code module:

http:www.mcgimpsey.com/excel/modules.html

For more on Getting Started with macros, see

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

 

[Guest]

Thank you! After compiling it runs well but it appears that it is a macro
only for this project. Is there a way of installing into a global macro,
since it is a function I'd like to use on many docs. I also was confused as
to how/where to put the wrapper code that would make this item accessible
from the toolbar.
It may have failed when I first tried it because I hadn't yet compiled the
code.

Jeff

Try this variation:

Public Sub ColumnsToText()
'J.E. McGimpsey http://www.mcgimpsey.com/excel/mergedata.html
Const sDELIM As String = " "
Dim vTxtArr As Variant
Dim rRng As Range
Dim nTop As Long
Dim i As Long
Dim j As Integer
Set rRng = Intersect(Selection, Selection.Parent.UsedRange)
vTxtArr = rRng.Value
nTop = UBound(vTxtArr, 1)
For i = 1 To nTop
For j = 2 To UBound(vTxtArr, 2)
vTxtArr(i, 1) = vTxtArr(i, 1) & sDELIM & vTxtArr(i, j)
Next j
Next i
ReDim Preserve vTxtArr(1 To nTop, 1 To 1)
rRng.Resize(, 1).Value = vTxtArr
End Sub

Change the delimiter as desired. This macro will work on the cells you
have selected when you call it.

Put this in a regular code module:

http:www.mcgimpsey.com/excel/modules.html

For more on Getting Started with macros, see

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

~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.com

 

[JE McGimpsey]

Thank you! After compiling it runs well but it appears that it is a macro
only for this project. Is there a way of installing into a global macro,
since it is a function I'd like to use on many docs. I also was confused as
to how/where to put the wrapper code that would make this item accessible
from the toolbar.
It may have failed when I first tried it because I hadn't yet compiled the
code.

One place to put it is your Personal Macro Workbook, which normally
resides in the Applications:Microsoft Office 2004:Office:Startup:Excel
folder. If it hasn't been generated yet, the easiest thing to do is
record a macro and choose the PMW from the Store Macro In dropdown on
the Record Macro dialog.

Wrapper code should also reside in that workbook. You should then attach
the macro to a toolbar button.

 

[JE McGimpsey]

Thank you! After compiling it runs well but it appears that it is a macro
only for this project. Is there a way of installing into a global macro,
since it is a function I'd like to use on many docs. I also was confused as
to how/where to put the wrapper code that would make this item accessible
from the toolbar.
It may have failed when I first tried it because I hadn't yet compiled the
code.

A good resource is David McRitchie's Getting Started with Macros and
User Defined Functions:


It's written from a WinXL perspective, but the majority of it translates
directly. Win's Personal.xls is implemented on the Mac as "Personal
Macro Workbook" (no quotes, no extension) in the
HD:Applications:Microsoft Office 2004:Office:Startup:Excel folder.

 

[Guest]

Thanks again,
Looks like I'll try and work on this after Xmas. See if I can figure it out.
Jeff

One place to put it is your Personal Macro Workbook, which normally
resides in the Applications:Microsoft Office 2004:Office:Startup:Excel
folder. If it hasn't been generated yet, the easiest thing to do is
record a macro and choose the PMW from the Store Macro In dropdown on
the Record Macro dialog.

Wrapper code should also reside in that workbook. You should then attach
the macro to a toolbar button.

~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.com

 

[Guest]

The personal workbook wrapper gives an error that a wrong number of
arguments or invalid property assignments are given:

Public Sub CTT_Button_Click()
ColumnsToText Selection, " "
End Sub

Take a look at

http://www.mcgimpsey.com/excel/mergedata.html

~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.com