Sunday, April 14, 2013

yogi_Split Rows Of Text Conditionally Into Two Columns Dates If Present At The Beginning On Left And The Rest On Right


                                          Google Spreadsheet   Post  #1127
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 15, 2013
user Karim Yousfi  :(http://productforums.google.com/forum/?zx=j6jg3qrn27zo#!category-topic/docs/spreadsheets/bJKy9OW8HPg)
Using RegEX Expressions to split a cell
Hi,

I am trying to solve a problem that I'm facing.

I have data in straight text format and I would like to split this data according to a delimiter that is found in the Cell. The problem I have is that not all the data has this delimiter.

For example (I hope I format this correctly so it is understandable)

A1=December 31: {Some text here}
A2={Some Text Here on December 31}
A3=December 30: {Some Text Here}

What i would like to do is split the data so that all the dates appear in one column and the text in the next column so it should look something like this

C                                         D
December 31               Some text here
                                  Some text here on December 31
December 30               Some Text Here

I used the split function and it worked for all text that had the date in it but ones that didn't it kept in the same column as the date column

I am trying to get my head around RegEx functions but can't seem to make it work..

Any idea would be appreciated.

Karim

---
Yogi,

Thanks for the reply.

The sheet can be found here  https://docs.google.com/spreadsheet/ccc?key=0AuxpMOqueBx9dEptMDl0UE0xa0l5V0VsWkd6R1R4YUE&usp=sharing

I have done this manually with the split but it doesn't keep the text in the the News Article column if there is nothing to split. I was using the ":" as the delimiter to split the cell but as you can see some cells don't have this delimiter. I had a thought of concatenating a ":" to begging every cell that doesn't have a ":" but started to read up on arrayformula and regex functions.

From my readings it seems that arrayformula with regexreplace and possibly len is what I might need but I'm new to this so I could be way off.

The cells in the sheet that I sent that I would like to split are A4:A9.

The logic

The cells contain data for news articles. The date is given in the first article for that day. Any subsequent articles for that day don't have a date just the text from the article but occurred on the same day as the first article above it with a date in it.

My logic was to split the cells with the ":" delimiter and then run an isblank over the New Article column which would move all the news articles into one column. 

This should work but I'm hoping to find a cleaner solution which will also help me understand the RegEx functions.

Let me know if there is anything else

----------------------------------------------------------------------------------------------------------
following is a solution to the problem