#1




Use formula to copy cell formats also
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 
#2




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" 
#3




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. 
#4




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] 
#5




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 
#6




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 
#7




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 
#8




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] 
#9




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




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] 
#11




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 
Tags 
concatenate w/ format 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Excel copy formula  KMS  Excel  1  08162011 03:02 PM 
need to check whether cell contains formula  etodem  Excel  2  10232010 07:33 PM 
Formula in a Cell with condition?  Learner7  Excel  1  07192010 10:10 AM 
Protect formula in cell?  markg2  Excel  4  05242010 11:50 AM 
Excel error: "Too many different cell formats"  enviroko  Excel  0  01092008 07:27 AM 