Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
No squares for me in 2000, 2003 or 2007
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
No squares for me in 2000, 2003 or 2007
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
Squares for me on Excel 2003 SP1 (XP Pro SP2), Dell Laptop. See attachment. I'll check a desktop machine in a little while...
Dont know what this is to be honest, but i cant help thing it is related to regional settings... I'll see if I cant find out more.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hmm... the plot thickens.... tried it on a colleagues laptop which is the exact same machine as mine and he is NOT getting the square...
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
Quote from ravenswoodshg, try removing the last 3 cells from the concatenation formula. Does the square go away?
Yes, it does.
If you copy the original, paste values to another cell, it shows up.
But if you type the whole thing in, it doesn't :yikes:
Edit: I retract that last comment. It appears that if a cell has a two or more linefeeds, and the last linefeed is followed with one or more spaces and nothing else, the square shows up. Very strange ...
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
I noticed the two or more line feeds thing also. I also think the concatenating spaces contributes to the problem. I am stumped.
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
How about a function that does the catenation and removes trailing linefeeds?
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
Great idea. Thanks.
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
My home version is 2003 SP2 so I can see the problem.
Try this revision
=SUBSTITUTE(B2& " " & C2 & B1 & B3 & " " & C3 & B1 & B4 & " " & C4 & B1 & B5 & " " & C5 & B1 & B6 & " " & C6," " & CHAR(10),"")
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
Andy,
Your formula worked like a charm. In retrospect, it seems so simple. I should have thought of it. Keep up the good work.
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
Or, perhaps you could use Edit>Replace to replace chr(13) with chr(10)
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
Text wrap was the key to get CHARs work!
Thanks bros!
ps. I must say that MS BS help would be better not to exist at all.
Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square
Old thread ==> closed.
Don’t have an account yet? Register yourself now and be a part of our community!