Remove line breaks in Excel

15-11-04-R06-SCCM-test-ee-post.xlsxMS Access will not allow using the top row of an Excel file for import as a header row if it has line breaks in it.  This page shows a method of removing them that involves the use of this function and does not require any VBA.

Open in new window

How do I implement =SUBSTITUTE(A1,CHAR(10),” “) when the cell containing the data and line breaks is cell G1?  Where would I put the actual formula for this to work?  A sample spreadsheet is attached.  Assistance is greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If cell G1 is the only problem, just go to that cell, place your cursor on the last character of the first line in the cell, then press the delete key until the lower line is pulled up. Add a space afterward, if necessary then press the enter key.

Note that link that you found had a good answer. Just highlight your header row and follow these instructions (doing this worked for me):

1) Press CTRL+H (i.e. shortcut for Find and Replace);

2) Click in the ‘Find what‘ field, hold down the ALT key and type 0010;. It may not seem like anything happened but you actually entered an invisible line break character;

3) Click in the ‘Replace with‘ field, press the spacebar once and click Find All. You will get a list of all cells containing a line break;

4) At this point you can choose to Replace All.

It may not be apparent but you may have caused some cells to have two blank spaces. You can easily find and get rid of them by repeating the previous 4 steps, using two blank spaces in the ‘Find What‘ field and one blank space in the ‘Replace with‘ field.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Another way is like this....

Select column G --> Press Ctrl + H --> In Find What box, click inside the box and press Ctrl + J --> In Replace with box, leave it blank --> Replace All.

That will do the trick and replace all the line breaks in column G.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Given the ugly nature of the headings, you should probably use an underscore to separate what is currently separated by the line breaks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.