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

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,"
"," ")
1
=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 '16 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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