=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," "))

2 Answers 2


Short answer

Use the following formula

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


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:

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

Using the regexreplace function, you can replace all instances of a newline, represented by "\n" and replacing it with "", thus transforming your string into a single line.

  • 1
    A little context explaining how this would help would make this a better answer.
    – ale
    Feb 3, 2016 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.