how do I concatenate in excel 2007 without losing formatting of cells?

Question

I have 5 cells in a row with the following values:

£3 £8 £2 £23 £54

I wish to concatenate them in excel to the following: £3|£8|£2|£23|£54
The probelm is the concatenate function does not transfer formating of cell weather % or £ or decimals etc...

So I get this: 3|8|2|23|54 - Which is not what I want.

Does anyone know how I can go about doing this, without manually having to type it into each cell?

Thank you

Answer 1

Can't do pound signs, $ will substitute. Place $ in F1, |$ in G1

=CONCATENATE ($F$1,A1,$G$1,B1,$G$1,C1, $G$1,D1,$G$1,E1)

Make F1 and G1 absolute if using for multiple times.



EDIT:- by making the F1 and G1 references absolute all you have to do is copy ythe formula down. Only A, B, C, D & E row numbers will change to suit.



You can't just format the cell to currency as what is in the cell is text, currency format only works with pure numeric data

Answer 2

I would say the reason you get that because the cell is displaying general numbers.



Don't know whether this will work but it's worth a try.



Highlight the Cell ► Format ► Cell ► Number ► Change the setting to Currency

Answer 3

Try the following:



="£"&B2&"|£"&C2&"|£"&D2&"|£"&E2&"|£"&F2



Where B2 is the £3, etc..



The & sign works the same as the CONCANTENATE function, just remeber to put " around the text you wish to add...