Solved Insert rows between different screenames in a chat history?

June 15, 2013 at 12:47:18
Specs: Windows Vista
Here is how my data appears after I paste it from Excel into Microsoft word (it is from a chat history):

10/26/2009 TV Si, te equivoque muuuuucho!
10/26/2009 TV :D
10/26/2009 LG ay bueno
10/26/2009 LG te iba a decir holandes primero
10/26/2009 LG jajaja
10/26/2009 TV oooooh!
10/26/2009 TV Y tu familia, todos son de paraguay?
10/26/2009 LG si señor

Here is how I want it to appear when I paste it from Excel into word:

10/26/2009 TV Si, te equivoque muuuuucho!
10/26/2009 TV :D

10/26/2009 LG ay bueno
10/26/2009 LG te iba a decir holandes primero
10/26/2009 LG jajaja

10/26/2009 TV oooooh!
10/26/2009 TV Y tu familia, todos son de paraguay?

10/26/2009 LG si señor

So basically (if I'm not mistaken) I need a new row to be inserted whenever the screename cell is different from the screename cell immediately before it.

I would do this manually if there weren't hundreds of hours of these messages that I need to organize. Thanks for anyone who is able to help!


See More: Insert rows between different screenames in a chat history?

Report •


#1
June 15, 2013 at 14:35:15
✔ Best Answer
This works, but has a minor glitch, that is easy to handle.

With your data like:


A B C D
1) 10/26/2009 TV Si, te equivoque muuuuucho!
2) 10/26/2009 TV :D
3) 10/26/2009 LG ay bueno
4) 10/26/2009 LG te iba a decir holandes primero
5) 10/26/2009 LG jajaja
6) 10/26/2009 TV oooooh!
7) 10/26/2009 TV Y tu familia, todos son de paraguay?
8) 10/26/2009 LG si señor

In Cell D1 enter the formula: =IF(B1=B2,"",CHAR(13))
Drag down as many rows as needed.

Next, Copy A1 thru D8

Open your Word Document,
and use Paste / Special / Unformatted Text
it should be the third choice down from the top.

Here is where the minor glitch come in.
You will notice that Excel inserts two quote marks.
One at the end of the line
and one on the blank new line.

You can simply do a Search / Replace
Search for the Quote mark, Replace with nothing
and your done.

MIKE

http://www.skeptic.com/


Report •

#2
June 15, 2013 at 19:16:13
Mike,

That's pretty neat. How did you figure that out?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
June 15, 2013 at 20:48:04
Seemed the simplest way.

Do a check for when column B changed and tack on a CR or NL with CHAR().

Only glitch is Excels habit of adding quotes.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 16, 2013 at 07:31:52
But what made you think that Word would accept the CR created by the formula and actually use it once pasted in? I like it!

My first thought was to separate the lines within Excel via a macro and then copy it over.

BTW...I tried various methods of not have the quotation marks show up by using different hidden characters but nothing worked.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
June 16, 2013 at 07:38:17
The Quotes are unavoidable
The Excel internal software adds them.
Trying to conform to the CVS standards is the reason I've been told.
The only workaround is to use a Macro, which you can find floating around on the web.

MIKE

http://www.skeptic.com/


Report •

#6
June 17, 2013 at 17:51:11
Thank you very much for your help, that is an excellent solution. Another possible solution given to me at another board, using a Macro, is as follows:

http://www.mrexcel.com/forum/excel-...

Thanks again!!


Report •


Ask Question