Retain formatting during concatenation

**NoCanDo** · 02-04-2009, 09:54 AM

Hi

I'm concatenating data in 5 cells (Author, Year, Title, City, Publisher), one of which is in italics (Title). When using concatenate (or &) the formatting is removed, and I need to be able to retain that formatting.

Example:
Col B
Aaron, M. (ed)
1999
The Body's Perilous Pleasures.
Endinburgh
Edinburgh University Press

Please Login or Register  to view this content.

Gives is:
Aaron, M. (ed) (1999) The Body's Perilous Pleasures. Endinburgh: Edinburgh University Press

Whereas I need:
Aaron, M. (ed) (1999) The Body's Perilous Pleasures. Endinburgh: Edinburgh University Press

Is there any way to do this?

Hope you can help.

NoCanDo

**NBVC** · 02-04-2009, 09:59 AM

As your name says... I think it's a NoCanDo

**Leith Ross** · 02-04-2009, 01:41 PM

Hello NoCanDo,

It can be done using VBA code. Here is the macro code. This has been added to the attached workbook. To run the macro, click the button labeled "Concatenate" on "Sheet1".

Please Login or Register  to view this content.

**NBVC** · 02-04-2009, 01:46 PM

Well, I guess maybe it can.... oops! Should've known better than not to trust VBA to do the trick...

Then its a YesCanDo.

**NoCanDo** · 02-04-2009, 03:15 PM

Many thanks to you guys for the assistance, Leith - looks good with the VB approach - will check it out. However, I've found an easier (to me) way.

Make sure the cells are formatted as you want them
Copy the cells required
Paste it all into MS Word
Highlight table it creates in MS Word and convert to text, using a 'space' as the separator when converting.
Highlght all paragraphs in MS Word
Paste back into MS Excel

Hey presto it works:

Aaron, M. (ed) 1999 The Body's Perilous Pleasures. Endinburgh: Edinburgh University Press
Aaron. J. & Walby, S. (eds) 1991 Out of The Margins. Women's studies in the nineties. London: Falmer Press

Thanks to all

NoCanDo (but I achieved in the end!)