entering a semi-colon between a concatenate

[kins]

Hi,


I have a list of data on the same row in different columns, and i need to total them, but I need to split them with a semi-colon. for example -


A1 B1 C1 D1 E1 F1 H1 (Summary)
01 (Blank) 07 (Blank) 08 10 01;07;08;10


I need it to read like it has in has one. But row 2 might have different sets of data i.e A2 might be blank and B2 might have data in.


Can anyone help in me achieving the outcome in H1?

Thanks in advance.

 

[mikerickson]

Try
=SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2), " ", ";")

 

[kins]

following on from this, the data in them cells comes from a formula that says if the cell is not blank, then take the info at the top of the pivot, so my other question is, is the anyway on putting a ; into this formula (below) so not only does it bring back what's in the top of the row, but also puts a ; before it? i.e ;and the value of $b$4??

=IF(B5="","",$B$4)

 

[kins]

thanks mike. i'll give it a whirl