Excel Functions LEFT, RIGHT, MID to extract data from String

[darin100K]

Hello.

I am using VBA to extract the comments from cells in Column B into the same row in Column G. The string in column G essentially has three main parts each separated by a comma. For example, XX XXX XXXX,YYY YY YYY,Z Z Z Z ZZZ. There are no spaces before or after the comma and each string may be different in terms of content and length.

I now need some magic to separate this string into separate columns. Back to the example:

If XX XXX XXXX,YYY YY YYY,Z Z Z Z ZZZ is stored in G6,

G7 should be XX XXX XXXX
G8 should be YYY YY YYY
G9 should be Z Z Z Z ZZZ

I've used LEFT, RIGHT, and MID before but only when I knew the exact lengths of the desired string.

As always, I'm amazed at the loyalty of our MrExcel members and the overall power of Excel.

Thank you to whoever helps me now and everyone that has in the past.

I love this website!

 

[darin100K]

I just realized why the third part was split into different rows. Duh, the third part of the comment contains a comma. If at all possible, could you keep that in place? The third part is essentially "Last name, First Name."


Thanks and sorry for my blunder!

 

[jindon]

OK
How are your comment actually look?

Can you post few samples?

 

[darin100K]

Here is an actual example of what I'm dealing with in the comments and what I hope to do.

Cell A2 = 07/01/2006
Cell B2 = 30 (no comment)

Cell A3 = 09/27/2006
Cell B3 = -5 (Comment reads "Call in <1 hour early, Entered on 09/27/2006, Entered by Smith, John")

Cell G3 = "Call in <1 hour early, Entered on 09/27/2006, Entered by Smith, John"

From here, I'd like the following to happen...

H3 = Call in <1 hour early
I3 = Entered on 09/27/2006
J3 = Entered by Smith, John

And again I would like this to loop through each cell in Column B that has an attached comment.

Thanks again. SO wonderful!

 

[jindon]

I guess there are line feed somewhere in the comment
try this...

Sub testme() 
    Dim myCell As Range 
    Dim myRng As Range 
    Dim x
    Dim txt As String

    With Worksheets("sheet1") 
        Set myRng = Nothing 
        On Error Resume Next 
        Set myRng = .Range("b:b").Cells.SpecialCells(xlCellTypeComments) 
        On Error GoTo 0 


        If Not myRng Is Nothing Then  
            For Each myCell In myRng.Cells
                With myCell.Offset(0, 5)
                    .Value = myCell.Comment.Text 
                    txt = Replace(.Value,Chr(10),"")
                    x = Split(txt,",")
                    .Offset(1).Resize(UBound(x) + 1).Value = Application.Transpose(x)
                End With
            Next myCell 
        End If 
    End With 


End Sub

 

[jindon]

J3 = Entered by Smith, John

does the last comma always come like this?

 

[darin100K]

Yes, the comma is always like that. Is it possible to do this without a macro utilizing multiple text functions? Or is there a different way I could input the comments to make it easier to separate them later?

Thanks again.

 

[jindon]

I don't think you can retrieve comment text without macro...
try

Sub testme() 
    Dim myCell As Range 
    Dim myRng As Range 
    Dim x
    Dim txt As String

    With Worksheets("sheet1") 
        Set myRng = Nothing 
        On Error Resume Next 
        Set myRng = .Range("b:b").Cells.SpecialCells(xlCellTypeComments) 
        On Error GoTo 0 


        If Not myRng Is Nothing Then  
            For Each myCell In myRng.Cells
                With myCell.Offset(0, 5)
                    .Value = myCell.Comment.Text 
                    txt = Replace(.Value,Chr(10),"")
                    x = Split(txt,",")
                    x(UBound(x)-1) = x(UBound(x)-1) & Chr(32) & x(UBound(x))
                    .Offset(1).Resize(UBound(x)).Value = x
                End With
            Next myCell 
        End If 
    End With 


End Sub

 

[darin100K]

Update:

They split very nicely into the three sections, but I still have them all going to the same column:

G2= full string with all three sections
G3= first part
G4= second part
G5= third part

Can you tweak this so the parts move into adjacent columns and not rows? For instance...

G2= full string with all three sections
H2= first part
I2= second part
J2= third part

Thanks again. You're amazing to keep helping me over the course of a couple of days.

 

[Bob Rooney]

Replace

.Resize(UBound(x)).Value = Application.Transpose(x)

with

.Resize(, UBound(x)).Value = x

and see if that resolves your problem

 

[jindon]

Change Application.Tanspose(x) To x will be fine...

Previous code has been edited.