Merge Cells

HI
I changed the datatype to Text for the whole spreadsheet but when I try to merge any cells I get the error 'different data types'

What I have is about 6 fields which contain address details
as some of the addresses are shorter that onthers I have gaps
for example one address could be
add1 add2 add3 add4 add5 zip
with all fields populated
another address could be
add1 add2 add3 add4 add5 zip
with add5 left blank
and some addresses would have different fields missing

I dont want to merger all the fields in to one, I just want the details in HI
I changed the datatype to Text for the whole spreadsheet but when I try to merge any cells I get the error 'different data types'

What I have is about 6 fields which contain address details
as some of the addresses are shorter that onthers I have gaps
for example one address could be
add1 add2 add3 add4 add5 zip
with all fields populated
another address could be
add1 add2 add3 add4 add5 zip
with add5 left blank
and some addresses would have different fields missing

I dont want to merger all the fields in to one, I just want the details in cosecutive fields.

I tried merging cells but it didnt work so is there any maco/piece of code I can use to do this?

Thanks in fields.

I tried merging cells but it didnt work so is there any maco/piece of code I can use to do this?

Thanks in advance

[Aladin Akyurek]

You don't have to change the data type at all.

Lets say that you have

add1 add2 add3 add4 add5 zip

in A2:F2

In G2 enter:

=A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2

will cocatenate ("merge") into a single field.

[Tap]

Thats Cool
but not exactly what I wanterd to do

first adress
add1 add2 add3 add4 add5 add6
Myhouse 10 East Rd Eastly York 123

second address
add1 add2 add3 add4 add5 add6
20 West Rd York 123

I want the layot as the first address
but the second I dont want empty ceels, I want the data to be empty
I want it to look like

first adress
add1 add2 add3 add4 add5 add6
Myhouse 10 East Rd Eastly York 123

second address
add1 add2 add3 add4 add5 add6
20 West Rd York 123


is there a way of doing this without manually changing every record

thanks again

[Aladin Akyurek]

On 2002-03-03 03:22, Tap wrote:
Thats Cool
but not exactly what I wanterd to do

first adress
add1 add2 add3 add4 add5 add6
Myhouse 10 East Rd Eastly York 123

second address
add1 add2 add3 add4 add5 add6
20 West Rd York 123

I want the layot as the first address
but the second I dont want empty ceels, I want the data to be empty
I want it to look like

first adress
add1 add2 add3 add4 add5 add6
Myhouse 10 East Rd Eastly York 123

second address
add1 add2 add3 add4 add5 add6
20 West Rd York 123


is there a way of doing this without manually changing every record

thanks again

=IF(LEN(A2),A2&" ","")&IF(LEN(B2),B2&" ","")&IF(LEN(C2),C2&" ","")&IF(LEN(D2),D2&" ","")&IF(LEN(E2),E2&" ","")&IF(LEN(F2),F2,"")




[ This Message was edited by: Aladin Akyurek on 2002-03-03 03:28 ]