Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-03-2011, 12:45 PM
mike_abc mike_abc is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Novice
Use formula to copy cell formats also
 
Join Date: Dec 2011
Posts: 9
mike_abc is on a distinguished road
Question 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
Reply With Quote
  #2  
Old 12-04-2011, 01:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,601
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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"
Reply With Quote
  #3  
Old 12-04-2011, 01:36 AM
mike_abc mike_abc is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Novice
Use formula to copy cell formats also
 
Join Date: Dec 2011
Posts: 9
mike_abc is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 12-06-2011, 02:05 AM
macropod's Avatar
macropod macropod is offline Use formula to copy cell formats also Windows 7 64bit Use formula to copy cell formats also Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,713
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 12-06-2011, 11:48 AM
mike_abc mike_abc is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Novice
Use formula to copy cell formats also
 
Join Date: Dec 2011
Posts: 9
mike_abc is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 12-06-2011, 01:00 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,601
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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
Reply With Quote
  #7  
Old 12-06-2011, 01:25 PM
mike_abc mike_abc is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Novice
Use formula to copy cell formats also
 
Join Date: Dec 2011
Posts: 9
mike_abc is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 12-06-2011, 03:16 PM
macropod's Avatar
macropod macropod is offline Use formula to copy cell formats also Windows 7 64bit Use formula to copy cell formats also Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,713
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #9  
Old 12-07-2011, 03:06 AM
mike_abc mike_abc is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Novice
Use formula to copy cell formats also
 
Join Date: Dec 2011
Posts: 9
mike_abc is on a distinguished road
Default

Yes, that means that only Word capitalizes the cell. At least mine does .
Thanks.
Reply With Quote
  #10  
Old 12-07-2011, 04:42 AM
macropod's Avatar
macropod macropod is offline Use formula to copy cell formats also Windows 7 64bit Use formula to copy cell formats also Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,713
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by mike_abc View Post
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]
Reply With Quote
  #11  
Old 12-10-2011, 01:55 AM
mike_abc mike_abc is offline Use formula to copy cell formats also Windows XP Use formula to copy cell formats also Office 2003
Novice
Use formula to copy cell formats also
 
Join Date: Dec 2011
Posts: 9
mike_abc is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
concatenate w/ format

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel copy formula KMS Excel 1 08-16-2011 03:02 PM
Use formula to copy cell formats also need to check whether cell contains formula etodem Excel 2 10-23-2010 07:33 PM
Use formula to copy cell formats also Formula in a Cell with condition? Learner7 Excel 1 07-19-2010 10:10 AM
Use formula to copy cell formats also Protect formula in cell? markg2 Excel 4 05-24-2010 11:50 AM
Excel error: "Too many different cell formats" enviroko Excel 0 01-09-2008 07:27 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft