Merging Cells

Hi

I have a list of a couple of thousand postcodes in a column in a 
spreadsheet. Each postcode occupies its own cell. Examples of each postcode 
might be AB10 or AB11 or AB12 etc. I want to cut and paste these postcodes 
to another sheet, many codes to be pasted into single cells.

I tried to merge the cells as they stand so that I could collectively copy 
and paste them but excel says that the cells contain multiple data values 
and won;t let me merge them.

Can anyone tell me how to do this without cutting and pasting the contents 
of each cell, one at a time please?

Many thanks
Dave 


0
curvedball (22)
8/31/2006 9:53:38 PM
excel.newusers 15348 articles. 2 followers. Follow

24 Replies
645 Views

Similar Articles

[PageSpeed] 44

I forgot to mention...the postcodes need to be comma delimted.


"David" <curvedball@yahoo.com> wrote in message 
news:6uednVHYqs0Xx2rZRVnygw@bt.com...
> Hi
>
> I have a list of a couple of thousand postcodes in a column in a 
> spreadsheet. Each postcode occupies its own cell. Examples of each 
> postcode might be AB10 or AB11 or AB12 etc. I want to cut and paste these 
> postcodes to another sheet, many codes to be pasted into single cells.
>
> I tried to merge the cells as they stand so that I could collectively copy 
> and paste them but excel says that the cells contain multiple data values 
> and won;t let me merge them.
>
> Can anyone tell me how to do this without cutting and pasting the contents 
> of each cell, one at a time please?
>
> Many thanks
> Dave
> 


0
curvedball (22)
8/31/2006 10:16:01 PM
David

You say "many" to a single cell.

You can combine data from many cells to one cell by using a formula like

=A1&B1&C1&D1&E1&F1 etc.

If "many" is a great whack you might do better with a User Defined Function.

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 & ","
    Next
    ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is  =ConCatRange(Sheet1!A1:A43) or your choice.

I would not use this on more than about 200 cells at a time.  Excel won't show
all the characters past about 1000 characters.

If not familiar with VBA and macros, see David McRitchie's site for more on 
"getting started". 

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

In the meantime.......... 

To create a General Module, hit ALT + F11 to open the Visual Basic Editor. 

Hit CRTL + R to open Project Explorer. 

Find your workbook/project and select it. 

Right-click and Insert>Module.  Paste the above code in there.  Save the 
workbook and hit ALT + Q to return to your workbook. 

Enter the formula as shown above. 


Gord Dibben Excel MVP 


On Thu, 31 Aug 2006 22:53:38 +0100, "David" <curvedball@yahoo.com> wrote:

>Hi
>
>I have a list of a couple of thousand postcodes in a column in a 
>spreadsheet. Each postcode occupies its own cell. Examples of each postcode 
>might be AB10 or AB11 or AB12 etc. I want to cut and paste these postcodes 
>to another sheet, many codes to be pasted into single cells.
>
>I tried to merge the cells as they stand so that I could collectively copy 
>and paste them but excel says that the cells contain multiple data values 
>and won;t let me merge them.
>
>Can anyone tell me how to do this without cutting and pasting the contents 
>of each cell, one at a time please?
>
>Many thanks
>Dave 
>

Gord Dibben  MS Excel MVP
0
Gord
8/31/2006 10:33:24 PM
David

The UDF I posted will give you comma-delimited postal codes.

To do it without the UDF 

=A1&","&B1&","&C1&","&D1 etc.


Gord

On Thu, 31 Aug 2006 23:16:01 +0100, "David" <curvedball@yahoo.com> wrote:

>I forgot to mention...the postcodes need to be comma delimted.
>
>
>"David" <curvedball@yahoo.com> wrote in message 
>news:6uednVHYqs0Xx2rZRVnygw@bt.com...
>> Hi
>>
>> I have a list of a couple of thousand postcodes in a column in a 
>> spreadsheet. Each postcode occupies its own cell. Examples of each 
>> postcode might be AB10 or AB11 or AB12 etc. I want to cut and paste these 
>> postcodes to another sheet, many codes to be pasted into single cells.
>>
>> I tried to merge the cells as they stand so that I could collectively copy 
>> and paste them but excel says that the cells contain multiple data values 
>> and won;t let me merge them.
>>
>> Can anyone tell me how to do this without cutting and pasting the contents 
>> of each cell, one at a time please?
>>
>> Many thanks
>> Dave
>> 
>

0
Gord
8/31/2006 11:12:58 PM
Many thanks for this. But I keep getting the message'Compile Error: 
Expected: list seperator or )' and the exclamation mark in the code becomes 
highlighted.

David

-- 
David Kitching Msc. Msc.
Managing Director
Natural Deco Ltd.
The Manor
Manor Lane
Loxley
Warwickshire CV35 9JX
UK.

Tel: +44 (0) 1789 470040
Mob: +44 (0) 7799 118518
www.naturaldeco.co.uk

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:uaoef25k9380dhn2f3ejqtgubhpsb1vre2@4ax.com...
> David
>
> You say "many" to a single cell.
>
> You can combine data from many cells to one cell by using a formula like
>
> =A1&B1&C1&D1&E1&F1 etc.
>
> If "many" is a great whack you might do better with a User Defined 
> Function.
>
> 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 & ","
>    Next
>    ConCatRange = Left(sbuf, Len(sbuf) - 1)
> End Function
>
> Usage is  =ConCatRange(Sheet1!A1:A43) or your choice.
>
> I would not use this on more than about 200 cells at a time.  Excel won't 
> show
> all the characters past about 1000 characters.
>
> If not familiar with VBA and macros, see David McRitchie's site for more 
> on
> "getting started".
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> In the meantime..........
>
> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>
> Hit CRTL + R to open Project Explorer.
>
> Find your workbook/project and select it.
>
> Right-click and Insert>Module.  Paste the above code in there.  Save the
> workbook and hit ALT + Q to return to your workbook.
>
> Enter the formula as shown above.
>
>
> Gord Dibben Excel MVP
>
>
> On Thu, 31 Aug 2006 22:53:38 +0100, "David" <curvedball@yahoo.com> wrote:
>
>>Hi
>>
>>I have a list of a couple of thousand postcodes in a column in a
>>spreadsheet. Each postcode occupies its own cell. Examples of each 
>>postcode
>>might be AB10 or AB11 or AB12 etc. I want to cut and paste these postcodes
>>to another sheet, many codes to be pasted into single cells.
>>
>>I tried to merge the cells as they stand so that I could collectively copy
>>and paste them but excel says that the cells contain multiple data values
>>and won;t let me merge them.
>>
>>Can anyone tell me how to do this without cutting and pasting the contents
>>of each cell, one at a time please?
>>
>>Many thanks
>>Dave
>>
>
> Gord Dibben  MS Excel MVP 


0
curvedball (22)
9/1/2006 8:33:51 AM
Hi David,

I'm not sure I understand your problem.
Why do you want to merge the cells before you copy them?

Surely you can just highlight the entire column right click on it
and select copy, then open your new sheet right click in A1
and select paste.

Or am I missing something here?

HTH
Martin 


0
mtmw (348)
9/1/2006 9:04:42 AM
....because I want all of the contents of every cell to be combined in to a 
single cell, comma delimited. [and preferably with a space as well, after 
each comma]. This is for putting the data into a postcode lokup database for 
determining shipping costs. About 2900 codes are to be split over 9 cells.


"MartinW" <mtmw@hotmail.invalid> wrote in message 
news:uHKPmWazGHA.4452@TK2MSFTNGP05.phx.gbl...
> Hi David,
>
> I'm not sure I understand your problem.
> Why do you want to merge the cells before you copy them?
>
> Surely you can just highlight the entire column right click on it
> and select copy, then open your new sheet right click in A1
> and select paste.
>
> Or am I missing something here?
>
> HTH
> Martin
> 


0
curvedball (22)
9/1/2006 11:54:14 AM
Everything between these two lines in Gord's function:
Function ConCatRange(CellBlock As Range) As String
End Function

goes into that General module in the VBE--including those two lines!

And then you'd use something like:
=ConCatRange(Sheet1!A1:A43)
(from sheet2, say)
or just
=ConCatRange(A1:A43)
from the same sheet.

You may want to take a look at Gord's instructions one more time.

David wrote:
> 
> Many thanks for this. But I keep getting the message'Compile Error:
> Expected: list seperator or )' and the exclamation mark in the code becomes
> highlighted.
> 
> David
> 
> --
> David Kitching Msc. Msc.
> Managing Director
> Natural Deco Ltd.
> The Manor
> Manor Lane
> Loxley
> Warwickshire CV35 9JX
> UK.
> 
> Tel: +44 (0) 1789 470040
> Mob: +44 (0) 7799 118518
> www.naturaldeco.co.uk
> 
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:uaoef25k9380dhn2f3ejqtgubhpsb1vre2@4ax.com...
> > David
> >
> > You say "many" to a single cell.
> >
> > You can combine data from many cells to one cell by using a formula like
> >
> > =A1&B1&C1&D1&E1&F1 etc.
> >
> > If "many" is a great whack you might do better with a User Defined
> > Function.
> >
> > 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 & ","
> >    Next
> >    ConCatRange = Left(sbuf, Len(sbuf) - 1)
> > End Function
> >
> > Usage is  =ConCatRange(Sheet1!A1:A43) or your choice.
> >
> > I would not use this on more than about 200 cells at a time.  Excel won't
> > show
> > all the characters past about 1000 characters.
> >
> > If not familiar with VBA and macros, see David McRitchie's site for more
> > on
> > "getting started".
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > In the meantime..........
> >
> > To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
> >
> > Hit CRTL + R to open Project Explorer.
> >
> > Find your workbook/project and select it.
> >
> > Right-click and Insert>Module.  Paste the above code in there.  Save the
> > workbook and hit ALT + Q to return to your workbook.
> >
> > Enter the formula as shown above.
> >
> >
> > Gord Dibben Excel MVP
> >
> >
> > On Thu, 31 Aug 2006 22:53:38 +0100, "David" <curvedball@yahoo.com> wrote:
> >
> >>Hi
> >>
> >>I have a list of a couple of thousand postcodes in a column in a
> >>spreadsheet. Each postcode occupies its own cell. Examples of each
> >>postcode
> >>might be AB10 or AB11 or AB12 etc. I want to cut and paste these postcodes
> >>to another sheet, many codes to be pasted into single cells.
> >>
> >>I tried to merge the cells as they stand so that I could collectively copy
> >>and paste them but excel says that the cells contain multiple data values
> >>and won;t let me merge them.
> >>
> >>Can anyone tell me how to do this without cutting and pasting the contents
> >>of each cell, one at a time please?
> >>
> >>Many thanks
> >>Dave
> >>
> >
> > Gord Dibben  MS Excel MVP

-- 

Dave Peterson
0
petersod (12005)
9/1/2006 12:33:32 PM
That means you would want to combine about 323 cells into each
composite cell, and with a comma between each postcode (with an average
length of 7 characters, say) this means you will have 2584 characters
in each combined cell.

Pete

David wrote:
> ...because I want all of the contents of every cell to be combined in to a
> single cell, comma delimited. [and preferably with a space as well, after
> each comma]. This is for putting the data into a postcode lokup database for
> determining shipping costs. About 2900 codes are to be split over 9 cells.
>
>
> "MartinW" <mtmw@hotmail.invalid> wrote in message
> news:uHKPmWazGHA.4452@TK2MSFTNGP05.phx.gbl...
> > Hi David,
> >
> > I'm not sure I understand your problem.
> > Why do you want to merge the cells before you copy them?
> >
> > Surely you can just highlight the entire column right click on it
> > and select copy, then open your new sheet right click in A1
> > and select paste.
> >
> > Or am I missing something here?
> >
> > HTH
> > Martin
> >

0
pashurst (2576)
9/1/2006 2:34:21 PM
Thats right...

As a matter of fact, I've just spent all day doing it manually.

-- 
David Kitching Msc. Msc.
Managing Director
Natural Deco Ltd.
The Manor
Manor Lane
Loxley
Warwickshire CV35 9JX
UK.

Tel: +44 (0) 1789 470040
Mob: +44 (0) 7799 118518
www.naturaldeco.co.uk

"Pete_UK" <pashurst@auditel.net> wrote in message 
news:1157121261.878072.135600@e3g2000cwe.googlegroups.com...
> That means you would want to combine about 323 cells into each
> composite cell, and with a comma between each postcode (with an average
> length of 7 characters, say) this means you will have 2584 characters
> in each combined cell.
>
> Pete
>
> David wrote:
>> ...because I want all of the contents of every cell to be combined in to 
>> a
>> single cell, comma delimited. [and preferably with a space as well, after
>> each comma]. This is for putting the data into a postcode lokup database 
>> for
>> determining shipping costs. About 2900 codes are to be split over 9 
>> cells.
>>
>>
>> "MartinW" <mtmw@hotmail.invalid> wrote in message
>> news:uHKPmWazGHA.4452@TK2MSFTNGP05.phx.gbl...
>> > Hi David,
>> >
>> > I'm not sure I understand your problem.
>> > Why do you want to merge the cells before you copy them?
>> >
>> > Surely you can just highlight the entire column right click on it
>> > and select copy, then open your new sheet right click in A1
>> > and select paste.
>> >
>> > Or am I missing something here?
>> >
>> > HTH
>> > Martin
>> >
> 


0
curvedball (22)
9/1/2006 3:25:22 PM
Me thinks there must be a better way of importing the data :)


Steve



On Fri, 01 Sep 2006 15:34:21 +0100, Pete_UK <pashurst@auditel.net> wrote=
:

> That means you would want to combine about 323 cells into each
> composite cell, and with a comma between each postcode (with an averag=
e
> length of 7 characters, say) this means you will have 2584 characters
> in each combined cell.
>
> Pete
>
> David wrote:
>> ...because I want all of the contents of every cell to be combined in=
  =

>> to a
>> single cell, comma delimited. [and preferably with a space as well,  =

>> after
>> each comma]. This is for putting the data into a postcode lokup  =

>> database for
>> determining shipping costs. About 2900 codes are to be split over 9  =

>> cells.
>>
>>
>> "MartinW" <mtmw@hotmail.invalid> wrote in message
>> news:uHKPmWazGHA.4452@TK2MSFTNGP05.phx.gbl...
>> > Hi David,
>> >
>> > I'm not sure I understand your problem.
>> > Why do you want to merge the cells before you copy them?
>> >
>> > Surely you can just highlight the entire column right click on it
>> > and select copy, then open your new sheet right click in A1
>> > and select paste.
>> >
>> > Or am I missing something here?
>> >
>> > HTH
>> > Martin
0
sj_walton (248)
9/1/2006 3:45:43 PM
David wrote:
> ...because I want all of the contents of every cell to be combined in to a 
> single cell, comma delimited. [and preferably with a space as well, after 
> each comma]. This is for putting the data into a postcode lokup database for 
> determining shipping costs. About 2900 codes are to be split over 9 cells.
> 
> 
> "MartinW" <mtmw@hotmail.invalid> wrote in message 
> news:uHKPmWazGHA.4452@TK2MSFTNGP05.phx.gbl...
>> Hi David,
>>
>> I'm not sure I understand your problem.
>> Why do you want to merge the cells before you copy them?
>>
>> Surely you can just highlight the entire column right click on it
>> and select copy, then open your new sheet right click in A1
>> and select paste.
>>
>> Or am I missing something here?
>>
>> HTH
>> Martin
>>
> 
> 
When you say a postcode lookup database. What type of database?
Most database program support importing of various file types.
As one other poster suggested, there must be a better way.
With more info someone here might be able to suggest one.

gls858
0
gls858 (460)
9/1/2006 4:07:47 PM
I did that. I really did, although this is new to me. I'm not entirely IT 
illiterate though and I did try a few intelligent variations, but Excel is 
very precise, rightly so, and unless you know what you're doing...anyway, I 
couldn't get it to work.

-- 
David Kitching Msc. Msc.
Managing Director
Natural Deco Ltd.
The Manor
Manor Lane
Loxley
Warwickshire CV35 9JX
UK.

Tel: +44 (0) 1789 470040
Mob: +44 (0) 7799 118518
www.naturaldeco.co.uk

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:44F8289C.155F6DBA@verizonXSPAM.net...
> Everything between these two lines in Gord's function:
> Function ConCatRange(CellBlock As Range) As String
> End Function
>
> goes into that General module in the VBE--including those two lines!
>
> And then you'd use something like:
> =ConCatRange(Sheet1!A1:A43)
> (from sheet2, say)
> or just
> =ConCatRange(A1:A43)
> from the same sheet.
>
> You may want to take a look at Gord's instructions one more time.
>
> David wrote:
>>
>> Many thanks for this. But I keep getting the message'Compile Error:
>> Expected: list seperator or )' and the exclamation mark in the code 
>> becomes
>> highlighted.
>>
>> David
>>
>> --
>> David Kitching Msc. Msc.
>> Managing Director
>> Natural Deco Ltd.
>> The Manor
>> Manor Lane
>> Loxley
>> Warwickshire CV35 9JX
>> UK.
>>
>> Tel: +44 (0) 1789 470040
>> Mob: +44 (0) 7799 118518
>> www.naturaldeco.co.uk
>>
>> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> news:uaoef25k9380dhn2f3ejqtgubhpsb1vre2@4ax.com...
>> > David
>> >
>> > You say "many" to a single cell.
>> >
>> > You can combine data from many cells to one cell by using a formula 
>> > like
>> >
>> > =A1&B1&C1&D1&E1&F1 etc.
>> >
>> > If "many" is a great whack you might do better with a User Defined
>> > Function.
>> >
>> > 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 & ","
>> >    Next
>> >    ConCatRange = Left(sbuf, Len(sbuf) - 1)
>> > End Function
>> >
>> > Usage is  =ConCatRange(Sheet1!A1:A43) or your choice.
>> >
>> > I would not use this on more than about 200 cells at a time.  Excel 
>> > won't
>> > show
>> > all the characters past about 1000 characters.
>> >
>> > If not familiar with VBA and macros, see David McRitchie's site for 
>> > more
>> > on
>> > "getting started".
>> >
>> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
>> >
>> > In the meantime..........
>> >
>> > To create a General Module, hit ALT + F11 to open the Visual Basic 
>> > Editor.
>> >
>> > Hit CRTL + R to open Project Explorer.
>> >
>> > Find your workbook/project and select it.
>> >
>> > Right-click and Insert>Module.  Paste the above code in there.  Save 
>> > the
>> > workbook and hit ALT + Q to return to your workbook.
>> >
>> > Enter the formula as shown above.
>> >
>> >
>> > Gord Dibben Excel MVP
>> >
>> >
>> > On Thu, 31 Aug 2006 22:53:38 +0100, "David" <curvedball@yahoo.com> 
>> > wrote:
>> >
>> >>Hi
>> >>
>> >>I have a list of a couple of thousand postcodes in a column in a
>> >>spreadsheet. Each postcode occupies its own cell. Examples of each
>> >>postcode
>> >>might be AB10 or AB11 or AB12 etc. I want to cut and paste these 
>> >>postcodes
>> >>to another sheet, many codes to be pasted into single cells.
>> >>
>> >>I tried to merge the cells as they stand so that I could collectively 
>> >>copy
>> >>and paste them but excel says that the cells contain multiple data 
>> >>values
>> >>and won;t let me merge them.
>> >>
>> >>Can anyone tell me how to do this without cutting and pasting the 
>> >>contents
>> >>of each cell, one at a time please?
>> >>
>> >>Many thanks
>> >>Dave
>> >>
>> >
>> > Gord Dibben  MS Excel MVP
>
> -- 
>
> Dave Peterson 


0
curvedball (22)
9/1/2006 7:55:29 PM
I ended up:

creating a second column where each cell contained just a comma and a space.

creating a thrid column where the other two were combined.

Copying the whole third column and pasting it into Word.

Merging the relevant cells in Word

and then special pasting each merged cell contents as unformatted text.

Then each bit was cut and pasted back into each of the 9 cells.

Took all day but it's done!

-- 
David Kitching Msc. Msc.
Managing Director
Natural Deco Ltd.
The Manor
Manor Lane
Loxley
Warwickshire CV35 9JX
UK.

Tel: +44 (0) 1789 470040
Mob: +44 (0) 7799 118518
www.naturaldeco.co.uk

"David" <curvedball@yahoo.com> wrote in message 
news:_bmdnS0fyqz-DWXZRVnygw@bt.com...
> Thats right...
>
> As a matter of fact, I've just spent all day doing it manually.
>
> -- 
> David Kitching Msc. Msc.
> Managing Director
> Natural Deco Ltd.
> The Manor
> Manor Lane
> Loxley
> Warwickshire CV35 9JX
> UK.
>
> Tel: +44 (0) 1789 470040
> Mob: +44 (0) 7799 118518
> www.naturaldeco.co.uk
>
> "Pete_UK" <pashurst@auditel.net> wrote in message 
> news:1157121261.878072.135600@e3g2000cwe.googlegroups.com...
>> That means you would want to combine about 323 cells into each
>> composite cell, and with a comma between each postcode (with an average
>> length of 7 characters, say) this means you will have 2584 characters
>> in each combined cell.
>>
>> Pete
>>
>> David wrote:
>>> ...because I want all of the contents of every cell to be combined in to 
>>> a
>>> single cell, comma delimited. [and preferably with a space as well, 
>>> after
>>> each comma]. This is for putting the data into a postcode lokup database 
>>> for
>>> determining shipping costs. About 2900 codes are to be split over 9 
>>> cells.
>>>
>>>
>>> "MartinW" <mtmw@hotmail.invalid> wrote in message
>>> news:uHKPmWazGHA.4452@TK2MSFTNGP05.phx.gbl...
>>> > Hi David,
>>> >
>>> > I'm not sure I understand your problem.
>>> > Why do you want to merge the cells before you copy them?
>>> >
>>> > Surely you can just highlight the entire column right click on it
>>> > and select copy, then open your new sheet right click in A1
>>> > and select paste.
>>> >
>>> > Or am I missing something here?
>>> >
>>> > HTH
>>> > Martin
>>> >
>>
>
> 


0
curvedball (22)
9/1/2006 8:06:06 PM
David

There is no exclamation mark in my code.

I think that when you copied the code you also copied the Usage instructions.

Just copy the part from 

Function ConCatRange(CellBlock As Range) As String

down to and including

End Function


Gord

On Fri, 1 Sep 2006 09:33:51 +0100, "David" <curvedball@yahoo.com> wrote:

>Many thanks for this. But I keep getting the message'Compile Error: 
>Expected: list seperator or )' and the exclamation mark in the code becomes 
>highlighted.
>
>David

Gord Dibben  MS Excel MVP
0
Gord
9/1/2006 9:02:16 PM
Too late, Gord, he's already done it using Word. Anyway, Dave told him
that yesterday.

Pete

Gord Dibben wrote:
> David
>
> There is no exclamation mark in my code.
>
> I think that when you copied the code you also copied the Usage instructions.
>
> Just copy the part from
>
> Function ConCatRange(CellBlock As Range) As String
>
> down to and including
>
> End Function
>
>
> Gord
>
> On Fri, 1 Sep 2006 09:33:51 +0100, "David" <curvedball@yahoo.com> wrote:
>
> >Many thanks for this. But I keep getting the message'Compile Error:
> >Expected: list seperator or )' and the exclamation mark in the code becomes
> >highlighted.
> >
> >David
> 
> Gord Dibben  MS Excel MVP

0
pashurst (2576)
9/1/2006 9:47:30 PM
Yesterday!!

Where has the time gone?<g>

I'm definitely getting lapped.


Gord


On 1 Sep 2006 14:47:30 -0700, "Pete_UK" <pashurst@auditel.net> wrote:

>Too late, Gord, he's already done it using Word. Anyway, Dave told him
>that yesterday.
>
>Pete
>
>Gord Dibben wrote:
>> David
>>
>> There is no exclamation mark in my code.
>>
>> I think that when you copied the code you also copied the Usage instructions.
>>
>> Just copy the part from
>>
>> Function ConCatRange(CellBlock As Range) As String
>>
>> down to and including
>>
>> End Function
>>
>>
>> Gord
>>
>> On Fri, 1 Sep 2006 09:33:51 +0100, "David" <curvedball@yahoo.com> wrote:
>>
>> >Many thanks for this. But I keep getting the message'Compile Error:
>> >Expected: list seperator or )' and the exclamation mark in the code becomes
>> >highlighted.
>> >
>> >David
>> 
>> Gord Dibben  MS Excel MVP

0
Gord
9/1/2006 9:59:09 PM
Today for us in the USA.  <vbg>

Gord Dibben wrote:
> 
> Yesterday!!
> 
> Where has the time gone?<g>
> 
> I'm definitely getting lapped.
> 
> Gord
> 
> On 1 Sep 2006 14:47:30 -0700, "Pete_UK" <pashurst@auditel.net> wrote:
> 
> >Too late, Gord, he's already done it using Word. Anyway, Dave told him
> >that yesterday.
> >
> >Pete
> >
> >Gord Dibben wrote:
> >> David
> >>
> >> There is no exclamation mark in my code.
> >>
> >> I think that when you copied the code you also copied the Usage instructions.
> >>
> >> Just copy the part from
> >>
> >> Function ConCatRange(CellBlock As Range) As String
> >>
> >> down to and including
> >>
> >> End Function
> >>
> >>
> >> Gord
> >>
> >> On Fri, 1 Sep 2006 09:33:51 +0100, "David" <curvedball@yahoo.com> wrote:
> >>
> >> >Many thanks for this. But I keep getting the message'Compile Error:
> >> >Expected: list seperator or )' and the exclamation mark in the code becomes
> >> >highlighted.
> >> >
> >> >David
> >>
> >> Gord Dibben  MS Excel MVP

-- 

Dave Peterson
0
petersod (12005)
9/1/2006 10:05:53 PM
I tried it that way, I honestly did. Then reverted to including content that 
should, clearly, have been replaced. I entered the phrase 'A2:A150' and also 
A2,A150' just to be sure. But, whilst the help and advice of experts such as 
yourselves is greatly appreciated, do understand that it's sometimes easy to 
forget just what degree of detail is needed in describing how to do 
someting. I've been an IT trainer and do have some experience of this. 
Imagine telling someone how to drive a car, who's never seen one 
before...how would you describe changing gear? In terms of a gearstick? Of 
second and fourth etc? What's a gear??

Anyway, I got there in the end. And to my mind, it's a shame that Excel has 
so much trouble doing something so simple. Concatenating data in cells. Word 
can do it!

Thanks for all your interest.

-- 
David Kitching Msc. Msc.
Managing Director
Natural Deco Ltd.
The Manor
Manor Lane
Loxley
Warwickshire CV35 9JX
UK.

Tel: +44 (0) 1789 470040
Mob: +44 (0) 7799 118518
www.naturaldeco.co.uk

"Pete_UK" <pashurst@auditel.net> wrote in message 
news:1157147250.839279.20880@i42g2000cwa.googlegroups.com...
> Too late, Gord, he's already done it using Word. Anyway, Dave told him
> that yesterday.
>
> Pete
>
> Gord Dibben wrote:
>> David
>>
>> There is no exclamation mark in my code.
>>
>> I think that when you copied the code you also copied the Usage 
>> instructions.
>>
>> Just copy the part from
>>
>> Function ConCatRange(CellBlock As Range) As String
>>
>> down to and including
>>
>> End Function
>>
>>
>> Gord
>>
>> On Fri, 1 Sep 2006 09:33:51 +0100, "David" <curvedball@yahoo.com> wrote:
>>
>> >Many thanks for this. But I keep getting the message'Compile Error:
>> >Expected: list seperator or )' and the exclamation mark in the code 
>> >becomes
>> >highlighted.
>> >
>> >David
>>
>> Gord Dibben  MS Excel MVP
> 


0
curvedball (22)
9/1/2006 10:07:17 PM
Oh no, it wasn't yesterday - it was about 1:30pm (my time, it being
11:10pm now). It just seems like ages ago. <bg>

Pete

Gord Dibben wrote:
> Yesterday!!
>
> Where has the time gone?<g>
>
> I'm definitely getting lapped.
>
>
> Gord
>
>
> On 1 Sep 2006 14:47:30 -0700, "Pete_UK" <pashurst@auditel.net> wrote:
>
> >Too late, Gord, he's already done it using Word. Anyway, Dave told him
> >that yesterday.
> >
> >Pete
> >
> >Gord Dibben wrote:
> >> David
> >>
> >> There is no exclamation mark in my code.
> >>
> >> I think that when you copied the code you also copied the Usage instructions.
> >>
> >> Just copy the part from
> >>
> >> Function ConCatRange(CellBlock As Range) As String
> >>
> >> down to and including
> >>
> >> End Function
> >>
> >>
> >> Gord
> >>
> >> On Fri, 1 Sep 2006 09:33:51 +0100, "David" <curvedball@yahoo.com> wrote:
> >>
> >> >Many thanks for this. But I keep getting the message'Compile Error:
> >> >Expected: list seperator or )' and the exclamation mark in the code becomes
> >> >highlighted.
> >> >
> >> >David
> >> 
> >> Gord Dibben  MS Excel MVP

0
pashurst (2576)
9/1/2006 10:08:25 PM
Yep.  Starting points for instructions can be difficult to pinpoint.  I used to
joke to my co-workers that I really don't want to tell them how to set their
alarm clocks and get ready for work--much less document their path to work--or
how to find the on/off switch on the pc.

But I read Gord's instructions and they seemed quite reasonable to me <bg>.

If I were you, I'd revisit his instructions and try it once more.  If you have
questions, post back with the steps you followed (which could be difficult to
document <vbg>).

You may find that Gord's function useful later--or you may find that you need
something different that can't be done in MSWord.

Just a thought (or two).

David wrote:
> 
> I tried it that way, I honestly did. Then reverted to including content that
> should, clearly, have been replaced. I entered the phrase 'A2:A150' and also
> A2,A150' just to be sure. But, whilst the help and advice of experts such as
> yourselves is greatly appreciated, do understand that it's sometimes easy to
> forget just what degree of detail is needed in describing how to do
> someting. I've been an IT trainer and do have some experience of this.
> Imagine telling someone how to drive a car, who's never seen one
> before...how would you describe changing gear? In terms of a gearstick? Of
> second and fourth etc? What's a gear??
> 
> Anyway, I got there in the end. And to my mind, it's a shame that Excel has
> so much trouble doing something so simple. Concatenating data in cells. Word
> can do it!
> 
> Thanks for all your interest.
> 
> --
> David Kitching Msc. Msc.
> Managing Director
> Natural Deco Ltd.
> The Manor
> Manor Lane
> Loxley
> Warwickshire CV35 9JX
> UK.
> 
> Tel: +44 (0) 1789 470040
> Mob: +44 (0) 7799 118518
> www.naturaldeco.co.uk
> 
> "Pete_UK" <pashurst@auditel.net> wrote in message
> news:1157147250.839279.20880@i42g2000cwa.googlegroups.com...
> > Too late, Gord, he's already done it using Word. Anyway, Dave told him
> > that yesterday.
> >
> > Pete
> >
> > Gord Dibben wrote:
> >> David
> >>
> >> There is no exclamation mark in my code.
> >>
> >> I think that when you copied the code you also copied the Usage
> >> instructions.
> >>
> >> Just copy the part from
> >>
> >> Function ConCatRange(CellBlock As Range) As String
> >>
> >> down to and including
> >>
> >> End Function
> >>
> >>
> >> Gord
> >>
> >> On Fri, 1 Sep 2006 09:33:51 +0100, "David" <curvedball@yahoo.com> wrote:
> >>
> >> >Many thanks for this. But I keep getting the message'Compile Error:
> >> >Expected: list seperator or )' and the exclamation mark in the code
> >> >becomes
> >> >highlighted.
> >> >
> >> >David
> >>
> >> Gord Dibben  MS Excel MVP
> >

-- 

Dave Peterson
0
petersod (12005)
9/2/2006 12:46:07 AM
Hi David,
To me it looks
like you are trying to create  a  .csv  (Comma Separated Values)
file,   which Excel is perfectly able to create with a  File, Save As
and for more troublesome stuff you can use a macro

  Comma Separated Values, .CSV files
  http://www.mvps.org/dmcritchie/excel/csv.htm

Are you Sure you need everything in one cell.  After you get
everything in one cell, what do you do with it.   If it is simply
paste it into notepad  then you could have created the flat
file directly out of Excel.
  Create CSV files from each sheet in selection, Dave Peterson, 2005-09-15, public.excel
  http://groups.google.com/groups?threadm=4329B772.A496EC7D%40verizonXSPAM.net
=--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"David" <curvedball@yahoo.com> wrote in message news:6uednVHYqs0Xx2rZRVnygw@bt.com...
> Hi
>
> I have a list of a couple of thousand postcodes in a column in a
> spreadsheet. Each postcode occupies its own cell. Examples of each postcode
> might be AB10 or AB11 or AB12 etc. I want to cut and paste these postcodes
> to another sheet, many codes to be pasted into single cells.
>
> I tried to merge the cells as they stand so that I could collectively copy
> and paste them but excel says that the cells contain multiple data values
> and won;t let me merge them.
>
> Can anyone tell me how to do this without cutting and pasting the contents
> of each cell, one at a time please?
>
> Many thanks
> Dave
>
>


0
9/2/2006 1:18:56 AM
But saving as a .csv file, which I tried, just seemed to produce a file 
which, when opened, looked exactly the same as the .xls, with everything in 
cells. OK, I now know that if I rename the csv to a txt, I can open it in 
wordpad and get my data in a column without cells, copy it all and paste 
special it as unformatted text. But that's what I did anyway, using word.

I need the data in nine cells because it's used to generate a table in a sql 
database where a website will check a custoemrs postcode and, depending on 
which column the postcode occurs in, it can apply a shipping charge 
appropriate for listed quantity ordered.

Cheers
David

-- 
David Kitching Msc. Msc.
Managing Director
Natural Deco Ltd.
The Manor
Manor Lane
Loxley
Warwickshire CV35 9JX
UK.

Tel: +44 (0) 1789 470040
Mob: +44 (0) 7799 118518
www.naturaldeco.co.uk

"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message 
news:ewMei4izGHA.1252@TK2MSFTNGP04.phx.gbl...
> Hi David,
> To me it looks
> like you are trying to create  a  .csv  (Comma Separated Values)
> file,   which Excel is perfectly able to create with a  File, Save As
> and for more troublesome stuff you can use a macro
>
>  Comma Separated Values, .CSV files
>  http://www.mvps.org/dmcritchie/excel/csv.htm
>
> Are you Sure you need everything in one cell.  After you get
> everything in one cell, what do you do with it.   If it is simply
> paste it into notepad  then you could have created the flat
> file directly out of Excel.
>  Create CSV files from each sheet in selection, Dave Peterson, 2005-09-15, 
> public.excel
> 
> http://groups.google.com/groups?threadm=4329B772.A496EC7D%40verizonXSPAM.net
> =--
> HTH,
> David McRitchie, Microsoft MVP - Excel
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
> "David" <curvedball@yahoo.com> wrote in message 
> news:6uednVHYqs0Xx2rZRVnygw@bt.com...
>> Hi
>>
>> I have a list of a couple of thousand postcodes in a column in a
>> spreadsheet. Each postcode occupies its own cell. Examples of each 
>> postcode
>> might be AB10 or AB11 or AB12 etc. I want to cut and paste these 
>> postcodes
>> to another sheet, many codes to be pasted into single cells.
>>
>> I tried to merge the cells as they stand so that I could collectively 
>> copy
>> and paste them but excel says that the cells contain multiple data values
>> and won;t let me merge them.
>>
>> Can anyone tell me how to do this without cutting and pasting the 
>> contents
>> of each cell, one at a time please?
>>
>> Many thanks
>> Dave
>>
>>
>
> 


0
curvedball (22)
9/2/2006 6:36:53 PM
On Fri, 01 Sep 2006 21:06:06 +0100, David <curvedball@yahoo.com> wrote:

> I ended up:
>
> creating a second column where each cell contained just a comma and a =
 =

> space.
>
> creating a thrid column where the other two were combined.
>
> Copying the whole third column and pasting it into Word.
>
> Merging the relevant cells in Word
>
> and then special pasting each merged cell contents as unformatted text=
..
>
> Then each bit was cut and pasted back into each of the 9 cells.
>
> Took all day but it's done!
>

if you were going to use word you could have just replace *newline* with=
  =

", "

-- =

Steve (3)
0
sj_walton (248)
9/2/2006 7:28:22 PM
So your database will only have 9 entries - fair enough
Just all seems odd manipulation of data for end result.

Steve


On Sat, 02 Sep 2006 19:36:53 +0100, David <curvedball@yahoo.com> wrote:

> But saving as a .csv file, which I tried, just seemed to produce a fil=
e
> which, when opened, looked exactly the same as the .xls, with everythi=
ng  =

> in
> cells. OK, I now know that if I rename the csv to a txt, I can open it=
 in
> wordpad and get my data in a column without cells, copy it all and pas=
te
> special it as unformatted text. But that's what I did anyway, using wo=
rd.
>
> I need the data in nine cells because it's used to generate a table in=
 a  =

> sql
> database where a website will check a custoemrs postcode and, dependin=
g  =

> on
> which column the postcode occurs in, it can apply a shipping charge
> appropriate for listed quantity ordered.
>
> Cheers
> David
0
sj_walton (248)
9/2/2006 7:36:02 PM
Reply:

Similar Artilces:

Auto change formula in cells when source is changed
Good day experts; If specific cells have the same formula for example: C1 = Product( A1;B1) C16 = Product(A16;B16) C17= Product(A17;B17) And so on ... What formula should i write in C16, C17, ... so that: when the formula in the source cell, C1, is changed the formulas in C16, C17, ... would automatically change accordingly. I tried "=C1" it does not work Thanks in advance Carlo carlo wrote: > Good day experts; > > If specific cells have the same formula for example: > C1 = Product( A1;B1) > C16 = Product(A16;B16) > C17= Product(A17;B17) > And so on ... ...

Linking many cells
I wish to link many cells in (e.g. A1:40) in sheet 1 to A1:A40 in sheet 2 without doing it one by one so that whaever I type in A1 sheet 1 appears in A1 sheet 2. Please any idea? dk As long as your 'from' and 'to' blocks are structured identically (eg 1 column of forty rows in your example), it's straightforward. Click in the first 'to' cell, Sheet1!A1. Type the '=' sign, then click in the first 'from' cell, Sheet2!A1, then hit enter. Your formula in Sheet!A1 should read =Sheet2!A1 (look for it in the formula bar). Grab the fill handle (th...

Cell Reference 01-12-10
What I am looking for is that when I enter a formula (In cell B1) to pick up value in A1. Now I need value from cell A5 in cell B2. Next value I need in B3 is A9. Everytime I have to change the cell values manually in column B. Formula I use : - =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) Now I need value from cell H336 and I manually change H331 to H336 shown below =IF(OR(AK64<$D$4,AK64=$D$4),H336,0) Any way to make this automated. Thanks Ankur Bhateja ankur.bhateja@hotmail.com Instead of =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) you could say =IF(AK64<=$D$4,H331,0) ...

decimal spaces toolong when mail merging from excel
I recive data in an excel spreadsheet that contains in one column a number expressed as " 5.55" which is supposed to be a percentage, but when I try to mailmerge the data, it mailmerges as "5.55061561650660". In other words it carries out the decimal place more than 2 digits, which is not what I want. I have tried formatting the cell, rounding, hand typing the information in another column and deleting the column that contained the original information (which I am sure was edited from 5.55061561650660.) How can I fix this problem so that when I mail merge it comes ou...

why do i get #### in my formula cell?
I don't really know much about Excel, and my formula (it's just simple arithmetic formulas) returns with ##### displayed on the cell, with a tooltip showing the cell's correct value when I hover over it. airn, make the column wider and see if that helps -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "airn" <airn@discussions.microsoft.com> wrote in message news:C6F4DD7C-3D7D-475C-894D-0F7745735666@microso...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

How to show the month of the referenced cell (containing a date)
If a cell holds the date "2/1/08", what formula can I use that will give me the following result: "Feb - 08" I tried =Month(a2)......but I just get the number of the month. Thanks. =TEXT(A2,"mmm - yy") Or simply =A2 with the cell custom formatted mmm - yy HTH. Best wishes Harald "Dave K" <fred.sheriff@gmail.com> skrev i melding news:165b03da-19ba-40b8-b9fe-77b17ef045b5@d45g2000hsc.googlegroups.com... > If a cell holds the date "2/1/08", what formula can I use that will > give me the following result: > > "Feb - 08&...

Conditional Formating: linking to display another cell
Hello, I am trying to make a traffic light with symbols and I've read to 'use a separate cell for the dropdown choices, with their resulting value linked into the formatted cell through an IF function, using the character that you want to display.' So: =IF someone enters '1' in B8, THEN display contents of $C$4 (will it display font and attributes?) =IF someone enters '2' in B8, THEN display contents of $C$5 =IF someone enters '3' in B8, THEN display contents of $C$6 But I've been reading everywhere and CF is very new to me and I need ...

Cell with email address
I have a cell where I just want to record an email address. However, whenever I enter it tries to connect to Outlook, thinking I want to send an email. How can I stop this? -- Les Les It shouldn't try to connect unless you click on it but you can precede the entry with a single apostrophe or press Ctrl+Z after entry (undo) and a second entry should stop it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Les" <Les@discussions.microsoft.com> wrote in message news:9B53EE8A-A0A8-4AFD-A09F-116DDE063747@microsoft.co...

can I make cell "yes" equal 1, "no" equal 0
in another cell? =IF(AND(A1<>"yes",A1<>"no"),"",(A1="yes")*1+(A1="no")*0) -- HTH RP (remove nothere from the email address if mailing direct) "can I make cell yes equal 1, no equa" <can I make cell "yes" equal 1, "no" equa@discussions.microsoft.com> wrote in message news:8E222344-E390-4D39-B82D-39586CC55A64@microsoft.com... > Picky but I don't think that you need the " +(A1="no")*0 " Just =IF(AND(A1<>"yes",A1<>"no"),"&quo...

Using Jet to read excel file returns blank for last cell
With VB6 I am opening an Excel file to query the contents using ADO. I am then using the contents of the worksheet to create a format file that is used for a bulk insert into SQL Server. We designed the program this way so that users can use a spreadsheet to import data in any format they want and process the data based on values set in the spreadsheet. This keeps us from having to design new tables and format files for new record layouts which come down pretty often. Everything works fine usually. There is just one perculiarity that is happening with the Excel file. Sometimes, th...

Find a value in cells
Hi, all, I have something like this : a 1 b 4 c 5 d 3 I'm doing a max function in the number column and it returns me '5' But, after that, I want to know the letter which corresponds to the max number How can I do that? Thanks Nic -- nicgendron ------------------------------------------------------------------------ nicgendron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25151 View this thread: http://www.excelforum.com/showthread.php?threadid=386417 Let's say that...

Detecting Case of Text in a Cell
Hi All I know it's possible to change the case of text in a cell in Excel using Lower, Upper and Proper, but is there a function that tells me the current case of the text? A user has a spreadsheet of catalogue items, some in proper case and some in upper. He wants to seperate those in upper case into a separate sheet but I can't think of an easy way of doing it. Can anyone help? Colin -- Colin Vicary ------------------------------------------------------------------------ Colin Vicary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10472 View this...

Getting cells to automatically delete spaces
Hi all I have got a very large spreadsheet where the information is presente as: 111111/ 1 Notice the two spaces after the forward slash. There's about 10'00 cells in the column with varying numbers but all in the above format. I need to get the two spaces deled so that it will appear as: 111111/1 Is there a way i can get excel to proccess this for me. Any help will be much appreciated. Thanks in advance. Darre -- Message posted from http://www.ExcelForum.com Darren, Select the required area or a whole sheet, and do a simple Replace (Edit menu), asking Excel to replace s...

Identifying LOCKED cells
Is there a way to identify locked cells, other than going through the processof looking at each cell's format? Here's by quandry. I am working a worksheet which has both locked and unlocked cells. Unfortunately some of the locked cells should be unlocked so that data can be input. Thanks for your help To reply to this message, remove "mand" from my address. Thanks I've dragged the lock button onto a toolbar. When I click on a cell, I can glance at that and see if it's depressed (not suicidal!) or not. Tools|Customize|Commands Tab|Format category. Look for "Lo...

enter value in a cell from a combo box
How to build a combo box to be able to select a value and enter in the cell 1. Go to View > Toolbars > Control Toolbox 2. Click on the "Combobox" icon once and draw one on your worksheet (hold down the ALT button to fit to a cell). 3. Right-click on the Combobox and go to Properties. 4. In the ListFillRange type in the range of cells that contain the values to fill the combobox, like A1:A5. 5. In the LinkedCell field type a cell reference that will contain the user's selection (ie E2). 6. Click on the Exit Design Mode icon and close the toolbar. HTH Jason Atlanta, GA ...

paste linked cells show "0"
I have a number of cells in a range (lets say A14:J52). Some of these cells are "paste linked" from cells on other sheets. Unfortunately, when the original cell is empty, the value of the "paste linked" cell shows as "0". (not just blank like the original) Is there a way to keep the "paste linked" cell showing "0"? If not, how would I go about using VBA to systematically go through the above described range and delete those zeros? At the point I ran this code, any "0" could be deleted from the range safely whether it ...

Cell colour change automatically
Is it possible to create a formula to automatically turn a cell a given colour? Example: - If I type the word "Yes" in a cell I would like the cell to turn red automatically, If I type the word "No" in a cell I would like the cell to turn green automatically. Is this possible? If yes please recreate a formula to assist me. Many thanks -=- dustyv Take a look at "Conditional Formatting" in XL Help. In article <45794F52-0D61-4B8F-8562-A3E9E6F957BE@microsoft.com>, dustyv <dustyv@discussions.microsoft.com> wrote: > Is it possible to create a formul...

Copy cell to a new cell
Hi I have a cell value in the format 90TN82C128004K00. I would like to copy the first two digits to another cell i.e. 90 and follow it up with a dummy code 00CPSS00 which will be standard for all values. the new cell should have the following value 9000CPSS00. Is there an automated function that can do this for me. Hi, If you data is in A1 enter in B1: =LEFT(A1,2)&"00CPSS00" Regards, -- AP "mohd21uk" <u20517@uwe> a �crit dans le message de news:5e5292da6a2d1@uwe... > Hi > > I have a cell value in the format 90TN82C128004K00. I would like to copy the ...

Converting cells
Hello! My problem is that I cannot see the $ sign in Format Cell window when I choose the Currency format. Even more, the cells are not changing for the currency format. Thanks for any help. Try making a custom format. Try this one: $#,##0.00;[Red]-$#,##0.00 --- Message posted from http://www.ExcelForum.com/ ...

Cell Reference question
This is probably a simple question, but I have never seen it before. I have been looking at some spredsheets sent by some co-workers, trying to figure out their structure etc... and I have seen some cell references where the reference starts with an =+'sheetname!e2 The + (plus sign) struck me as odd, I can't recall seeing that before. Is there some significance to it? Thanks Tom Believe it's a "legacy" behaviour for die-hard Lotus converts <g> The "+" is not necessary .. -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot&g...

get cell of function
Hi Can I somehow determine, in which column (A,B,C,...) a selfmade function is called? Or: Can I tell the function, placed f.e. in cell C1, to loop through the column left (in this example B) of it? thanks lot for help materphilch Dim rng as Range set rng = Application.Caller to see the value: MsgBox(rng.address) Gary's Student "masterphilch" wrote: > Hi > > Can I somehow determine, in which column (A,B,C,...) a selfmade function > is called? > Or: Can I tell the function, placed f.e. in cell C1, to loop through the > column left (in this example ...

Changing cell format
I have a worksheet that is populated with data exported from Access. The data in the Access table was serial numbers with an occasional (rare) alpha character. The column was formatted "Text" in Access. When I exported it to Excel, every value in this field (around 3000 entries), has an apostrophe (') inserted at the frontof the cell. Find and Replace does not see the ' How can I remove this so that I can use Fill>Series to populate the gaps in my data (there are many gaps)? Try copying the ' Access character and pasting it into the Replace menu. It could be...

Multiple lines in a single cell
How do I enter multiple lines in a single Excel cell? --thx, Joseph use alt & enter to put a carriage return in you might also like to check out format / cells- alignment tab and tick the wrap text box you might also need to manually resize the rows -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Joseph Mouhanna" <josephmo@hotmail.com> wrote in message news:9YadnXINBJB0FNDfRVn-tA@comcast.com... > How do I enter multiple lines in a single Excel cell? > > --thx, Joseph > ...

How many cells match a text string?
I have a sheet where I need to display a sum in one cell of a row that indicates how many other cells in the row contain text matching a given string. FIND or SEARCH can be used to tell whether any given cell contains a match, but is there a way, short of programming, to determine how many cells contain a match? Thanks, George Marshall George Try: =COUNTIF(A2:L2,"*text*") Andy "George Marshall" <george@NOSPAMmarshalls.org> wrote in message news:MPG.1a2f54291417b31e9896b9@msnews.microsoft.com... > I have a sheet where I need to display a sum in one cell ...