Google Sheets – How to Recombine Text into One Line

google sheetsimportxml

=index(ImportXml("https://www.google.com/finance?q=ibm", "//div[@class = 'companySummary']" ),0,1)

Put that anywhere in a Google sheet and it will give you a cell with a block of text. This text is in a paragraph form and takes up several lines. How do I use formulas to make it so the text is one line? It's possible because I can do it by hand if needed.

I've tried:

=join(" ",split(A1," "))

Best Answer

Short answer

Use the following formula

=SUBSTITUTE(A1,Char(10)," ")

Explanation

IMPORTXML added line feed characters which ASCII code is 10. An alternative to the use of Char(10) is to add a breakline (CTRL + ENTER) and enclose it in quotes marks:

=SUBSTITUTE(A1,"
"," ")

Related Solutions

Google Sheets – Pasting Text with Line Breaks Populates Multiple Cells

Based on http://productforums.google.com/forum/#!category-topic/docs/formatting/-uEh3jguVu0 you can " copy the info single cell by single cell and paste the information in edit mode of the receiving cell; i,e. double-click the cell first before you paste the single cell contents". Though this is hardly 'easy' so there may be a better answer out there...

Google Sheets Concatenate – How to Concatenate Several Columns into One

You need to filter down to non-empty cells, and then stack the results together:

={filter(A:A, len(A:A)); filter(B:B, len(B:B)); filter(C:C, len(C:C))}

This is the same as this answer by grayob which is unfortunately buried under outdated answers.