Hi,



Is there a way to copy the formats of individual cells when you concatenate them with the string concatenation operator (&) ? So far, I was only able to get the cell contents, but not the formatting.

Thanks,

Mike

I don't think this is possible without VBA, but depending on your formula maybe some CF would do the trick.
Please post a sample of your data showing " before" and "after"

Hi Pecoflyer,

Let's say I have this text residing in 6 adjacent cells (i.e. successive columns), in the same line:

Stipa lessingiana L. subsp. lessingiana Soo

So columns A, B are bold, column C is normal, column D is italic, column E is bold, column F is normal.

Now, I want to concatenate the whole lot and retain the formatting

Stipa lessingiana L. subsp. lessingiana (Heuffel) Soo - in one cell ! - say in column G.

instead of

Stipa lessingiana L. subsp. lessingiana (Heuffel) Soo

which is what I get when I use the "&" operator to concatenate the source cells.

Hi Mike,

Formulae cannot replicate formatting - only values. What you could do is to copy the range, paste it into Word as a table, then convert the table to text with, say, spaces as the separators. That will preserve your formatting.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

if it works, this is the solution ! But how do I convert a table in Word, with spaces, into a sentence (= text) ??? This would be it !

Thanks,

Mike

I haven't got Word 2003 here but maybe as follows :
Select your data - Copy
Open a Word Doc - Paste
The range is now under table layout
Go to Table
Select Convert
Select the separator character ( space)
OK
Copy Paste to Excel

Yes, it works ! Great, thanks a lot.
One more thing. Word capitalizes the first letter in the cell - is there a setting to cancel that ?

Mike

Hi Mike,

I don't get any formatting changes, including capitalisation, if I copy & paste your example from Excel to Word, then convert the resulting table to text in Word.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, that means that only Word capitalizes the cell. At least mine does .
Thanks.

Perhaps that has something to do with your autocorrect options (one of which is to capitalise the first letter in each cell) or your 'Smart paste' options (one of which is to adjust formatting when pasting from Excel), but I haven't been able to figure out a combination that replicates what your experience (I ordinarily use Word's default settings).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I had time today to look it up: Word (2003) has an Auto Correct option "Capitalize first letter of table cells". It was easy to find out why the table cell capitalization happens.

Cheers,

Mikey