how to remove break line space...

[ganeshkumar]

hi,
here with i have attached the XL sheet , in address column , please tel me how to remove space in the cell.

if you press F2 in A1 cell u can know what is the problem. hlp me to find out

[benishiryo]

hi ganeshkumar. there are actually 2 types of "spaces" inside. 1 is the line break you are referring to. the other is something called non-breaking space. that's something you cannot get rid of using TRIM. so either use a formula in column B
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(10),"")

or use CTRL + H to Find & Replace
the Line Space can be typed using CTRL + J
replace with nothing

the non-breaking space can be copied from cell A1. just press F2, select a single space at the end & press CTRL + C to copy. paste inside the Find & Replace Window. again, replace with nothing

[ganeshkumar]

thank u so much ... i got what i expected ....

[Marc L]

Hi !

• First insert a new column before column B.
• In B1 cell enter this formula : =CLEAN(A1)
• Copy down the formula until the last row used in column A.
• Select column B, Copy and Paste it in column A as value only.
• Delete column B.
• Select column A and in the cell properties the Wraptext must be set to False …

Other way is do to it via a VBA procedure :

Please Login or Register  to view this content.