Extracting only the last sentence from a cell

[dutink]

I have multiples cells which contains a paragraph of sentences and ends with capital letters of the speaker's name and position.

As an example, each cell has the following format:

" Good morning, ladies and gentlemen, and welcome to the www.jakkspacific.com third quarter 2010 earnings conference call. Today's call is being recorded. You will have the opportunity to ask questions at the end of the presentation. (Operator Instructions). I would now like to introduce Miss Genna Rosenberg, Senior Vice-President of Investor Relations. Please go ahead, Miss Rosenberg. GENNA ROSENBERG, VP OF CORPORATE COMMUNICATIONS AND IR, JAKKS PACIFIC, INC."

Is there a way to separate this single cell in to two cells where the first cell is the speech and second cell only contains "GENNA ROSENBERG, VP OF CORPORATE COMMUNICATIONS AND IR, JAKKS PACIFIC, INC."? Should I enter a formula or use vba for coding?

Thanks for any help!

 

[Rick Rothstein]

First off, I think you will need a macro as I assume you want to modify your original data to remove the upper case ending and move it into the next column. It was not clear to me whether those outer quote marks you show around your text were actually in the cells themselves or whether you include them to simply "neaten up" the display. I assumed they are not part of the cells' text. Here is a macro that will split apart the text (assumed to be in Column A) as requested...

Sub SplitOutNormalFromAllUpper()
  Dim R As Long, X As Long, Upper As Long, Data As Variant
  Data = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  For R = 1 To UBound(Data)
    For X = Len(Data(R, 1)) To 1 Step -1
      If Mid(Data(R, 1), X, 1) Like "[A-Z]" Then Upper = X
      If Mid(Data(R, 1), X, 1) Like "[a-z]" Then
        Data(R, 2) = Mid(Data(R, 1), Upper)
        Data(R, 1) = Trim(Left(Data(R, 1), Upper - 1))
        Exit For
      End If
    Next
  Next
  Range("A1").Resize(UBound(Data), 2) = Data
End Sub