String..string beans - NOT

[puskacpj]

Now I have another issue 'Imagine that'. There is one cell/field that has comments posted by multiple users. example:
<TABLE style="WIDTH: 684pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=912 border=0><COLGROUP><COL style="WIDTH: 684pt; mso-width-source: userset; mso-width-alt: 33353" width=912><TBODY><TR style="HEIGHT: 409.5pt" height=546><TD class=xl65 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 684pt; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 409.5pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=912 height=546>=====================================
Re-occuring issue from closed
SI # 1116274 Policies the have had UP payments corrected off the one day after the UP that still swept
agents account.
________________________________________
Kelly Tester <USWL1G6><USW1IG6>,
8/16/2011: Hi. This is the one we discussed.
________________________________________
Monica Tester <USWL45T><USWXTHE>,
8/16/2011: Larry - it looks like this occurred again.
________________________________________
Kelly Tester <USWL1G6><USW1IG6>,
8/31/2011: additional ticket came in.
QC closed as a duplicate is 2565

What I need to do is search each comment cell and break out the Name/code with date.
Ex:
Kelly Tester <USW1IG6>,
8/16/2011

Monica Tester <USWXTHE>,
8/16/2011

Kelly Tester <USW1IG6>,
8/31/2011

</TD></TR></TBODY></TABLE>
I will never know how many will be in each cell. This is what I have so far.

=SEARCH("<",F2,1) **Returns position of first <-this give the userid start but I want their name too.

=MID(F2,F1,25) **shows first name, userid and date

I know I can then use following to find second occurrence but, never know how many will be in the cell.

=FIND("<",F2,FIND("<",F2)+1)

Any ideas???

 

[Smitty]

Any ideas???

Yes, get your data out of one cell. Unfortunately, it's relatively useless the way it's set up now. You'll have a hard time getting a formula to return results consistently based on the varying size of the data in it as well, so with this structure you're only real alternative would be VBA.

Is there any reason you can't use a friendlier row/column format with each part of the record (Title, Name, Date, Comments, etc.) in a different column? If you can do that your job will be much easier.

 

[loweyecue]

Have you tried splitting the content into multiple cells using text to columns? May be you can use the "<" as the delimiter?

 

[puskacpj]

I wish I could change the format and create additional/fields-columns but I am working with HP Quality Center data that I export to Excel and run a post processing.

My code so far is sooooooooo much because I have to export, format, create a new column to age each ticket, create a sheet for the Aging by Application for all tickets, then I need a sheet with same format for all Closed tickets and one more for Open. PLUS I need the whole thing done again by: user assigned..all tickets, closed and open.

The Managers would like me to be able to pull the users and dates from the comments but I can't seem to get an answer on the HP QC site to see if there is a way to export the data into an exsisting workbook.

As of now, I have to enter all the vba code in the post processing using the sql query data selected. I need this to be '''dummy proof''' that is, the manager(s) that need this, just click the generate button and then queries run and exports to excel and runs the mountain of vba through post processing. The way it is going though, they can go to the pub and have a 'couple' while it is processing.

This is how the data looks when sent to excel: (all the blank lines show with a character - like boxes for all the spaces..I guess. Very crude.
<TABLE style="WIDTH: 390pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=520 border=0><COLGROUP><COL style="WIDTH: 390pt; mso-width-source: userset; mso-width-alt: 19017" width=520><TBODY><TR style="HEIGHT: 225pt; mso-height-source: userset" height=300><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 390pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 225pt; BACKGROUND-COLOR: transparent" width=520 height=300>======================================


test

________________________________________


Alice Gabrylski <uswom7u>,


7/1/2011: test


________________________________________


Alice Gabrylski <uswom7u>,


7/1/2011: test


________________________________________


Alice Gabrylski <uswom7u>,


7/1/2011: test.
======================================

I think I've gotten myself into a big mess. I tried the text to columns but all the data disappears..?? I think maybe the character boxes..can't describe are affecting the results of the search.


</TD></TR></TBODY></TABLE>