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]

Okay I made the changes by Bob and we are getting closer.

I now have...

G3=full string
G4=first part
H4=second part
I4=third part

I still need all results in row 3. Any other changes we could make?

Thanks.

 

[jindon]

OOps!
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

Edit: 16:09

 

[darin100K]

At last!!!

I really cannot thank you enough.

Best Regards,

Darin