Char(10) and blank lines

[Wanna Learn]

Hello This is my formula
=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHAR(10)&Address2&CHAR(10)&Address3&CHAR(10)&City&"
"&State&" "&TEXT(Zip,"00000") the problem is that when there is no address
2 or address 3 then I get a blank line .example below
"Mr. Jack Sparrow
Black Pearl Ltd
1400 Seaport Boulevard
0
0
Redwood City CA 94063"
Is there a way to correct this? thanks in advance

 

[Pete_UK]

You can do it like this:

=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHAR(10)&IF(Address2="","",Address2&CHAR(10))&IF(Address3="","",Address3&CHAR(10))&City&"
"&State&" "&TEXT(Zip,"00000")

Hope this helps.

Pete

 

[John C]

=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHAR(10)&IF(Address2="","",Address2&CHAR(10))&IF(Address3="","",Address3&CHAR(10))&City&"
"&State&" "&TEXT(Zip,"00000")

 

[Kevin B]

Try an if function for addresses 2 and 3, after Address1 put the following:

=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHAR(10)&IF(Address2="","",Address2&CHAR(10))&IF(Address2="","",Address4&CHAR(10))CHAR(10)&City&",
"&State&" "&TEXT(Zip,"00000")

If the there is no address2 and 3 the entire address block takes 3 lines, if
there's address1 and 2 the address block takes up 4 lines, etc...

 

[Gary''s Student]

Use a test. Say we usually have a first name in A1, a middle name in A2 and
a last name in A3. Our reduced formula would be:

=A1 & CHAR(10) & A2 & CHAR(10) & A3

Now if there is no middle name, then:

=A1 & IF(A2="","",CHAR(10) & A2) & CHAR(10) & A3

 

[Wanna Learn]

Thanks for your prompt response. I added the IF function after address 1
and 3 but I still get the same results. new formula
=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHAR(10)&IF(Address2="","",Address2&CHAR(10))&IF(Address3="","",Address3&CHAR(10)&City&"
"&State&" "&TEXT(Zip,"00000"))

if there is no address 2 or address 3 I want it to look like this
Mr. Jack Sparrow
Black Pearl Ltd
1400 Seaport Boulevard
Redwood City CA 94063


Not like this
"Mr. Jack Sparrow
Black Pearl Ltd
1400 Seaport Boulevard
0
0
Redwood City CA 94063"

thanks again!

 

[Pete_UK]

You should put a bracket after the CHAR(10) before &City and not at the end.

Perhaps your Address2 and Address3 are not actually blank, but contain a one
or more spaces (or char(160)).

Hope this helps.

Pete