Remove extra spaces within a cell

[a_avins]

Hi, i have number of lines within a cell in excel. My requirement would be to remove all extra spaces within a cell. The space could be a Unwanted CHR(10) before the fisrt or last line or extra space between two lines or extra space b/w two words. i have given a sample input and would need an out as given. I have given quotes just to indicate extra spaces. Thanx in advance


Input


"


Create project in

Provide user fields (like vertical)

Allocate resources


"


Output


"Create project in

Provide user fields (like vertical)

Allocate resources"

 

[oldchippy]

Hi a_avins,


Have you tried this in B1


=TRIM(A1) assuming data in A1

 

[DaveTurton]

if your text is in cell A1:


=TRIM(A1)

 

[a_avins]

if a cell has only one line then trim works, but if it has multiple lines seperate by ALT + ENTER, then trim does not work

 

[nazmul_muneer]

Dear a_avins,

Try this........


=TRIM(CLEAN(A1))


Regards,


Nazmul Muneer

 

[Debraj]

HI a_avins,


TRIM only check if any double or more space is in Left/Between/Right of any text, then it change it to SINGLE space..


Can you please try below for me..

=TRIM(SUBSTITUTE(A1,CHAR(10),""))

Regards,

Deb

 

[SirJB7]

@Debraj Roy

Hi!

Don't worry, I did it for you and it works fine. So don't be afraid of using that formula.

Regards!

 

[Sandy_16]

Hi All,


the Resultant for the above Trim+Subtitute & Trim+Clean is:

"Create project inProvide user fields (like vertical) Allocate resources"


Whereas a_avins is looking for the below O/P:


"Create project in

Provide user fields (like vertical)

Allocate resources"

 

[NARAYANK991]

Hi All ,


TRIM alone is sufficient.


The only problem is that when TRIM alone is used , the line breaks are shown as special characters ; click on Wrap Text to convert the line break special characters to displayed line breaks.


Narayan