|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Dave, Thanks, just found your reply to the other poster -
I have been trying to do this for hours - I knew it had to be possible
thank you thank you
"Dave Peterson" wrote:
> Select the range to convert to values
> Edit|Copy
> Edit|Paste special|Values
>
>
>
> Angie wrote:
> >
> > The answer to my prayers! How do you convert the column into values?
> >
> > "Dave Peterson" wrote:
> >
> > > I think I'd insert a new column E and put the formula there.
> > >
> > > You could copy that range to the other program.
> > >
> > > Or you could convert that new column E to values and delete columns C:D (or just
> > > column D???).
> > >
> > > xjaysfan wrote:
> > > >
> > > > Hi there
> > > > But I don't want formulas on the page..
> > > > As Well I am merging cell c and d and want the info to stay in d
> > > > I will end up copying and pasting to another program
> > > >
> > > > Thanks
> > > > Sandra
> > > >
> > > > "Batmans_Wife" wrote:
> > > >
> > > > > Thank you so much for sending me this formula. It worked perfectly and did
> > > > > exactly what I needed. I really appreciate your quick, helpful response.
> > > > >
> > > > > Sincerely,
> > > > > Michelle
> > > > >
> > > > > "Peo Sjoblom" wrote:
> > > > >
> > > > > > You can't, you can concatenate 2 cells into one by using a formula and
> > > > > > ampersand
> > > > > >
> > > > > > =A1&" "&B1
> > > > > >
> > > > > >
> > > > > > however stay away from merging cells, always cause more problems than what
> > > > > > it's worth and layout wise you can get very close without using it. I have
> > > > > > never seen a power user using merging
> > > > > >
> > > > > > Regards,
> > > > > >
> > > > > > Peo Sjoblom
> > > > > >
> > > > > > "Batmans_Wife" wrote:
> > > > > >
> > > > > > > I'm highlighting two cells in the same row, hitting format cells, alignment,
> > > > > > > merge cells and I'm getting the error message, "The selection contains
> > > > > > > multiple data values. Merging into one cell will keep the upper-left most
> > > > > > > data only." I want to be able to make the two seperate cells one without
> > > > > > > deleting anything and without having to cut and paste.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
Thank goodness for Google <vbg>!
kimceee wrote:
>
> Dave, Thanks, just found your reply to the other poster -
> I have been trying to do this for hours - I knew it had to be possible
> thank you thank you
>
> "Dave Peterson" wrote:
>
> > Select the range to convert to values
> > Edit|Copy
> > Edit|Paste special|Values
> >
> >
> >
> > Angie wrote:
> > >
> > > The answer to my prayers! How do you convert the column into values?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I think I'd insert a new column E and put the formula there.
> > > >
> > > > You could copy that range to the other program.
> > > >
> > > > Or you could convert that new column E to values and delete columns C:D (or just
> > > > column D???).
> > > >
> > > > xjaysfan wrote:
> > > > >
> > > > > Hi there
> > > > > But I don't want formulas on the page..
> > > > > As Well I am merging cell c and d and want the info to stay in d
> > > > > I will end up copying and pasting to another program
> > > > >
> > > > > Thanks
> > > > > Sandra
> > > > >
> > > > > "Batmans_Wife" wrote:
> > > > >
> > > > > > Thank you so much for sending me this formula. It worked perfectly and did
> > > > > > exactly what I needed. I really appreciate your quick, helpful response.
> > > > > >
> > > > > > Sincerely,
> > > > > > Michelle
> > > > > >
> > > > > > "Peo Sjoblom" wrote:
> > > > > >
> > > > > > > You can't, you can concatenate 2 cells into one by using a formula and
> > > > > > > ampersand
> > > > > > >
> > > > > > > =A1&" "&B1
> > > > > > >
> > > > > > >
> > > > > > > however stay away from merging cells, always cause more problems than what
> > > > > > > it's worth and layout wise you can get very close without using it. I have
> > > > > > > never seen a power user using merging
> > > > > > >
> > > > > > > Regards,
> > > > > > >
> > > > > > > Peo Sjoblom
> > > > > > >
> > > > > > > "Batmans_Wife" wrote:
> > > > > > >
> > > > > > > > I'm highlighting two cells in the same row, hitting format cells, alignment,
> > > > > > > > merge cells and I'm getting the error message, "The selection contains
> > > > > > > > multiple data values. Merging into one cell will keep the upper-left most
> > > > > > > > data only." I want to be able to make the two seperate cells one without
> > > > > > > > deleting anything and without having to cut and paste.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
See answer at your other post.
Gord
On Mon, 28 Apr 2008 05:52:01 -0700, Angie <Angie@discussions.microsoft.com>
wrote:
>The answer to my prayers! How do you convert the column into values?
>
>"Dave Peterson" wrote:
>
>> I think I'd insert a new column E and put the formula there.
>>
>> You could copy that range to the other program.
>>
>> Or you could convert that new column E to values and delete columns C:D (or just
>> column D???).
>>
>> xjaysfan wrote:
>> >
>> > Hi there
>> > But I don't want formulas on the page..
>> > As Well I am merging cell c and d and want the info to stay in d
>> > I will end up copying and pasting to another program
>> >
>> > Thanks
>> > Sandra
>> >
>> > "Batmans_Wife" wrote:
>> >
>> > > Thank you so much for sending me this formula. It worked perfectly and did
>> > > exactly what I needed. I really appreciate your quick, helpful response.
>> > >
>> > > Sincerely,
>> > > Michelle
>> > >
>> > > "Peo Sjoblom" wrote:
>> > >
>> > > > You can't, you can concatenate 2 cells into one by using a formula and
>> > > > ampersand
>> > > >
>> > > > =A1&" "&B1
>> > > >
>> > > >
>> > > > however stay away from merging cells, always cause more problems than what
>> > > > it's worth and layout wise you can get very close without using it. I have
>> > > > never seen a power user using merging
>> > > >
>> > > > Regards,
>> > > >
>> > > > Peo Sjoblom
>> > > >
>> > > > "Batmans_Wife" wrote:
>> > > >
>> > > > > I'm highlighting two cells in the same row, hitting format cells, alignment,
>> > > > > merge cells and I'm getting the error message, "The selection contains
>> > > > > multiple data values. Merging into one cell will keep the upper-left most
>> > > > > data only." I want to be able to make the two seperate cells one without
>> > > > > deleting anything and without having to cut and paste.
>>
>> --
>>
>> Dave Peterson
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Not without a User Defined Function like this one.
Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & " "
' for comma-delimited change above " " to ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
Usage is: =concatrange(A1:Z1)
Nore: blank cells will be ignored.
For similar methods with code see this search result from google
http://tinyurl.com/6ao6k4
Gord Dibben MS Excel MVP
On Wed, 6 Aug 2008 06:46:01 -0700, anthony561fl
<anthony561fl@discussions.microsoft.com> wrote:
>This works great. However, what if Im wanting to combine several columns, say
>50 or 100 columns worth of data? Id hate to have to enter each cell name in
>that formula. Is there a way to specify a range of columns or cells rather
>than each one before and after ampersands?
>
>
>
>"Peo Sjoblom" wrote:
>
>> You can't, you can concatenate 2 cells into one by using a formula and
>> ampersand
>>
>> =A1&" "&B1
>>
>>
>> however stay away from merging cells, always cause more problems than what
>> it's worth and layout wise you can get very close without using it. I have
>> never seen a power user using merging
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> "Batmans_Wife" wrote:
>>
>> > I'm highlighting two cells in the same row, hitting format cells, alignment,
>> > merge cells and I'm getting the error message, "The selection contains
>> > multiple data values. Merging into one cell will keep the upper-left most
>> > data only." I want to be able to make the two seperate cells one without
>> > deleting anything and without having to cut and paste.
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
You can copy the UDF into a general module in your Personal.xls which will
make it available for all open workbooks.
You could copy/paste it into a general module in a new workbook, save that
workbook as an Add-in which you would load through Tools>Add-ins.
I prefer the Add-in method so's I don't have to preface the Function with
the workbook name.
i.e. if saved in Personal.xls you must enter
=Personal.xls!ConCatRange(range)
If stored in the add-in =ConCatRange(range) is sufficient.
Gord
On Wed, 5 Nov 2008 06:22:00 -0800, rapid1 <rapid1@discussions.microsoft.com>
wrote:
>Works perfectly Gord - and please excuse my noobness, but how do I make the
>function available to all spreadsheets that I open without have to recreate
>the function each time?
>
>Ray D
>
>"Gord Dibben" wrote:
>
>> Not without a User Defined Function like this one.
>>
>> Function ConCatRange(CellBlock As Range) As String
>> Dim Cell As Range
>> Dim sbuf As String
>> For Each Cell In CellBlock
>> If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & " "
>> ' for comma-delimited change above " " to ","
>> Next
>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>> End Function
>>
>> Usage is: =concatrange(A1:Z1)
>>
>> Nore: blank cells will be ignored.
>>
>> For similar methods with code see this search result from google
>>
>> http://tinyurl.com/6ao6k4
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Wed, 6 Aug 2008 06:46:01 -0700, anthony561fl
>> <anthony561fl@discussions.microsoft.com> wrote:
>>
>> >This works great. However, what if Im wanting to combine several columns, say
>> >50 or 100 columns worth of data? Id hate to have to enter each cell name in
>> >that formula. Is there a way to specify a range of columns or cells rather
>> >than each one before and after ampersands?
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Answer
Notify me of replies