| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Char(10) and blank lines
Hello This is my formula
=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Char(10) and blank lines
You can do it like this:
=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(10)&IF(Address2="","",Address2&CHAR(10))&IF(Addr ess3="","",Address3&CHAR(10))&City&" "&State&" "&TEXT(Zip,"00000") Hope this helps. Pete "Wanna Learn" wrote in message ... Hello This is my formula =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Char(10) and blank lines
=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(10)&IF(Address2="","",Address2&CHAR(10))&IF(Addr ess3="","",Address3&CHAR(10))&City&"
"&State&" "&TEXT(Zip,"00000") -- John C "Wanna Learn" wrote: Hello This is my formula =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Char(10) and blank lines
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 -- Gary''s Student - gsnu200794 "Wanna Learn" wrote: Hello This is my formula =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Char(10) and blank lines
Try an if function for addresses 2 and 3, after Address1 put the following:
=FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(10)&IF(Address2="","",Address2&CHAR(10))&IF(Addr ess2="","",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... -- Kevin Backmann "Wanna Learn" wrote: Hello This is my formula =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Char(10) and blank lines
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&CHA R(10)&IF(Address2="","",Address2&CHAR(10))&IF(Addr ess3="","",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! "Kevin B" wrote: Try an if function for addresses 2 and 3, after Address1 put the following: =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(10)&IF(Address2="","",Address2&CHAR(10))&IF(Addr ess2="","",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... -- Kevin Backmann "Wanna Learn" wrote: Hello This is my formula =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Char(10) and blank lines
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 "Wanna Learn" wrote in message ... 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&CHA R(10)&IF(Address2="","",Address2&CHAR(10))&IF(Addr ess3="","",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! "Kevin B" wrote: Try an if function for addresses 2 and 3, after Address1 put the following: =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(10)&IF(Address2="","",Address2&CHAR(10))&IF(Addr ess2="","",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... -- Kevin Backmann "Wanna Learn" wrote: Hello This is my formula =FirstName&CHAR(10)&LastName&CHAR(10)&Address1&CHA R(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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FIND 1 char in cell of any 3 char =True | Excel Discussion (Misc queries) | |||
| Macro not recognizing blank lines as blank | Excel Discussion (Misc queries) | |||
| a problem with blank lines | Excel Worksheet Functions | |||
| 8500 cells with phone number(7 char.), wishing to add area code (10 char.) | Excel Discussion (Misc queries) | |||
| How do I change a data series from bars to lines in an Excel char. | Charts and Charting in Excel | |||
Linear Mode
