Wednesday, July 18, 2012

yogi_Process Text In A Cell To Show Each Item Separated By Specified Character(s) In A New Line

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #637   Jul 18, 2012

user lizjacksonberry said:
how to insert a new line in the replace function 
I have many text spreadsheets to edit and would like to enter a "carriage return" for a new line break in the replace function? i just can't find the answer anywhere. please help!
I originally had a formatted table in Word that I needed to transfer to a third program through google spreadsheet. So one of the changes I needed to make to transfer the table was to select all the new lines (carriage returns) in the word document using ^p and replaced them with the letters xx. Now that the document is in a google spreadsheet I am needing to reverse this function and replace the xx with carriage return (new lines) throughout the document. Unfortunately I cannot find a coded equivalent for ^p that I used in word for google spreadsheets. Excel on a windows computer has an equivalent of ALT + 0010. Does this code exist for google spreadsheets?

following is an array formula based solution which I have generalized a bit 
1) by including several possible separation characters that may be used in any combination
2) I have made a count of items in the processed text