How to get rid of additional space in the header row.

[Jagdev Singh]

Hi Experts,

Is there any easier way to get rid of additional space available in the headers of the attached sheet.

Regards,
JD

 

[jindon]

1) Select row1.
2) Hit Ctrl + H
3) Enter What: Ctrl + 0010 (these numbers MUST be entered from number pad)
4) Click on Replace All.

Should remove all Line Feed.

 

[deciog]

Jaidev Singh

Use the TRIM function (A1)

Decio

 

[NARAYANK991]

Hi ,

If you use Find & Replace , pressing CTRL J in the Find box also works.

Narayan

 

[Khalid NGO]

3) Enter What: Ctrl + 0010

Hi Jindon,

I believe you mean Alt+0010

Regards,

 

[Khalid NGO]

Hi @deciog

Trim will not work with CHAR(10)

Try:
=SUBSTITUTE(A1,CHAR(10),)

or this if CHAR(10) is always at first place.
=REPLACE(A1,1,1,)

Regards,

 

[Vishal Srivastava]

Hi Experts,

Is there any easier way to get rid of additional space available in the headers of the attached sheet.

Regards,
JD

Hi,

Use can also use
=CLEAN(a1)

Thanks & Regards,
CMA Vishal Srivastava

 

[Shteven]

These are by no means better, but here are two formulas if CHAR(10) is always the first character:
=IF(LEFT(A1,1)=CHAR(10),MID(A1,2,9^9),A1)
=MID(A1,SEARCH(CHAR(10),A1)+1,9^9)

 

[Jagdev Singh]

Hi All,

Thanks for your input on this. I tested Jindon and Narayan Sir's option and it worked well.

Again thanks folks for providing varies input on this.

Regards,
JD