Take CHAR(10) out automatically when redundant

In this formula, I tried using TRIM but apparently it doesn't acknowledge CHAR(10), but only redundant spaces. If I don't have the referenced filled out, it will still enter a line break. How do I go about eliminating CHAR(10) if the cell before it is empty?

=TRIM(CONCAT('1'!A47,CHAR(10),'1'!B54,CHAR(10),'1'!B56,CHAR(10),'1'!B57,CHAR(10),'1'!B58,CHAR(10),'1'!B59,CHAR(10),'1'!B60,CHAR(10),'1'!B61,CHAR(10),'1'!B62,CHAR(10),'1'!B63,CHAR(10),'1'!B64,CHAR(10),'1'!B65,CHAR(10),'1'!B66,CHAR(10),'1'!B67,CHAR(10),'1'!B68,CHAR(10),'1'!B69))

If one or more of your referenced cells are empty, then you will end up with two line feed characters together, so you could put a SUBSTITUTE function around your formula to change two CHAR(10)'s with one.

Hope this helps.

Pete

Originally Posted by burrjc

=TRIM(CONCAT('1'!A47,CHAR(10),'1'!B54,CHAR(10),'1'!B56,CHAR(10),'1'!B57,CHAR(10),'1'!B58,CHAR(10),'1'!B59,CHAR(10),'1'!B60,CHAR(10),'1'!B61,CHAR(10),'1'!B62,CHAR(10),'1'!B63,CHAR(10),'1'!B64,CHAR(10),'1'!B65,CHAR(10),'1'!B66,CHAR(10),'1'!B67,CHAR(10),'1'!B68,CHAR(10),'1'!B69))

This doesn't answer your question, but online documentation seems to suggest (I don't have the CONCAT function in my version of Excel, so i cannot test it) that you can simplify your above formula like this...

=TRIM(CONCAT('1'!A47,CHAR(10),'1'!B54,CHAR(10),'1'!B56:B69))

Now, assuming the above is correct, you should be able to do what you want with this untested (obviously given the above) formula...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCAT('1'!A47,CHAR(10),'1'!B54,CHAR(10),'1'!B56),REPT(CHAR(10),13),CHAR(10)),REPT(CHAR(10),5),CHAR(10)),REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),2),CHAR(10))

The CLEAN function takes ALL of the CHAR(10) out and turns it into a jumble of words.

Originally Posted by burrjc

The CLEAN function takes ALL of the CHAR(10) out and turns it into a jumble of words.

Did you see the formula I posted in Message #4?

Unfortunately =TRIM(CONCAT('1'!A47,CHAR(10),'1'!B54,CHAR(10),'1'!B56:B69)) doesn't work

Did you try my suggestion in Post #3, i.e.:

=SUBSTITUTE(TRIM(CONCAT('1'!A47,CHAR(10),'1'!B54,CHAR(10),'1'!B56,CHAR(10),'1'!B57,CHAR(10),'1'!B58,CHAR(10),'1'!B59,CHAR(10),'1'!B60,CHAR(10),'1'!B61,CHAR(10),'1'!B62,CHAR(10),'1'!B63,CHAR(10),'1'!B64,CHAR(10),'1'!B65,CHAR(10),'1'!B66,CHAR(10),'1'!B67,CHAR(10),'1'!B68,CHAR(10),'1'!B69)),CHAR(10)&CHAR(10),CHAR(10))

Hope this helps.

Pete

Originally Posted by Pete_UK

Did you try my suggestion in Post #3, i.e.:

=SUBSTITUTE(TRIM(CONCAT('1'!A47,CHAR(10),'1'!B54,CHAR(10),'1'!B56,CHAR(10),'1'!B57,CHAR(10),'1'!B58,CHAR(10),'1'!B59,CHAR(10),'1'!B60,CHAR(10),'1'!B61,CHAR(10),'1'!B62,CHAR(10),'1'!B63,CHAR(10),'1'!B64,CHAR(10),'1'!B65,CHAR(10),'1'!B66,CHAR(10),'1'!B67,CHAR(10),'1'!B68,CHAR(10),'1'!B69)),CHAR(10)&CHAR(10),CHAR(10))

Hope this helps.

Pete

Yes, this is better but there's still 1 dead space between a couple things, which is weird. Prior to SUBSTITUTE, there were 5 line breaks, and now there are 3.

=SUBSTITUTE(TRIM(CONCAT('Pricing 1'!A17,CHAR(10),'Pricing 1'!B24,CHAR(10),'Pricing 1'!B26,CHAR(10),'Pricing 1'!B27,CHAR(10),'Pricing 1'!B28,CHAR(10),'Pricing 1'!B29,CHAR(10),'Pricing 1'!B30,CHAR(10),'Pricing 1'!B31,CHAR(10),'Pricing 1'!B32,CHAR(10),'Pricing 1'!B33,CHAR(10),'Pricing 1'!B34,CHAR(10),'Pricing 1'!B35,CHAR(10),'Pricing 1'!B36,CHAR(10),'Pricing 1'!B37,CHAR(10),'Pricing 1'!B38)),CHAR(10)&CHAR(10),CHAR(10))

Originally Posted by Rick Rothstein

By the way, given that you do not seem to have a solution yet, why did you mark this thread as SOLVED?

That is also what I'm wondering.... My ADHD has been insane the past couple weeks, every day is a surprise to melately, lol.

Originally Posted by burrjc

… Yes, this is better but there's still 1 dead space between a couple things ...

Well then presumably you had 3 CHAR(10)s together - the first 2 would have been replaced by a single one, but you would still have the other one which will give you a double line-break. You could have another SUBSTITUTE around the one I gave you, replacing 2 char(10)'s with a single one, but then if you had 5 char(10)'s together you would still end up with a double one. You could avoid it completely by changing your formula like this:

=SUBSTITUTE(SUBSTITUTE(IF('1'!A47="","",'1'!A47&CHAR(10)) & IF('1'!B54="","",'1'!B54&CHAR(10)) & IF('1'!B56="","",'1'!B56&CHAR(10)) & IF('1'!B57="","",'1'!B57&CHAR(10)) & IF('1'!B58="","",'1'!B58&CHAR(10)) & IF('1'!B59="","",'1'!B59&CHAR(10)) & IF('1'!B60="","",'1'!B60&CHAR(10)) & IF('1'!B61="","",'1'!B61&CHAR(10)) & IF('1'!B62="","",'1'!B62&CHAR(10)) & IF('1'!B63="","",'1'!B63&CHAR(10)) & IF('1'!B64="","",'1'!B64&CHAR(10)) & IF('1'!B65="","",'1'!B65&CHAR(10)) & IF('1'!B66="","",'1'!B66&CHAR(10)) & IF('1'!B67="","",'1'!B67&CHAR(10)) & IF('1'!B68="","",'1'!B68&CHAR(10)) & IF('1'!B69="","",'1'!B69&CHAR(10)) & "$$",CHAR(10)&"$$",""),"$$","")

so the line feed character only appears if a cell contains something. A special character "$$" is added onto the end, so the final CHAR(10) can be removed, and if the cells are all blank (so no CHAR(10)'s appear anywhere), then a final SUBSTITUTE will remove the remaining "$$".

I can't try out solutions with TEXTJOIN or CONCAT in my version of Excel.

Hope this helps.

Pete

Originally Posted by WideBoyDixon

With TEXTJOIN:

Please Login or Register  to view this content.

WBD

TEXTJOIN WORKS!!! Thanks alot everyone. This seems to be the easiest.