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!

 

[venkat1926]

try this macro

Sub test()
'the string is in G6
Range("G6").Select
    Selection.TextToColumns Destination:=Range("G6"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
        
    Range(Range("g6"), Range("G6").End(xlToRight)).Select
    Selection.Copy
    Range("G7").Select
    Selection.PasteSpecial Transpose:=True
    Range("G6").Select
End Sub

 

[darin100K]

Thanks for the quick response! I'm currently using the snippet below to extract the comments. Could you help me combine the two?

Sub testme()
Dim myCell As Range
Dim myRng As Range


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


If myRng Is Nothing Then
'do nothing--no comments
Else
For Each myCell In myRng.Cells
myCell.Offset(0, 5).Value = myCell.Comment.Text
Next myCell
End If
End With


End Sub

 

[venkat1926]

suppose there are some comments (comma delimited) in G6 then
I do text to column G6,H6,I6 and transpose them to
G7,G8,G9. now in your data base I do not know whether there are some other data in H6 and I6 and also In G7 to G9. so it would be helpful if you post a small extract of your sheet .

greetings
venkat

 

[jindon]

Select the cell first and try

Sub test()
Dim x
With Selection
   If InStr(.Text,",") > 0 Then
      x = Split(.Text,",")
      .Offset(1).Resize(UBound(x) + 1).Value = x
   End If
End With
End Sub

 

[darin100K]

well, here's a brief description of the worksheet, "Calc". Starting in A2, column A contains a range of dates. The number of dates in column A is dependent on other parts of the macro. Starting in B2, column B contains a range of integers. Most of these cells have comments attached to them. For every date in Column A, there is a corresponding integer in column B. Cells C2 and D2 are the only cells that cannot be affected by this operation. So I currently was looping for all cells in column B with comments and then extracting the comments 5 columns to the right. Now i'm wondering how I can add onto that and say extract the comments to column G (five columns away from B) and then run the code you provided for each cell in column B with comments.

I hope I'm making myself clear. Thanks for the patience and follow-through.

 

[jindon]

try

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


    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 
                    x = Split(.Value,",")
                    .Offset(1).Resize(UBound(x) + 1).Value = x
                End With
            Next myCell 
        End If 
    End With 


End Sub

 

[darin100K]

I tried this and unfortunately I could not get it to work. This is what happened...

Column G does have the entire comments from it's corresponding cell in Column B. However, I then get the first segment of the comment repeated four times in the next four rows of Column G.

Any ideas?

 

[jindon]

OOPs!
Can you just change one line that is

.Offset(1).Resize(UBound(x) + 1).Value = x

To

.Offset(1).Resize(UBound(x) + 1).Value = Application.Transpose(x)

 

[darin100K]

Well, it definitely seems like you're on to something here, but I'm still not getting the desired results. I'm sorry if it's because of a poor explanation, but I really appreciate your efforts.

I now get the complete comment in say G3, the first part in G4, the second part in G5, most of the third part in G6, and a small portion of the third part in G7.

My hopes, assuming the full text is in G3, is to move the first part to H3, the second part to I3, and the entire third part to J3.

Can you maybe make a couple more tweaks on my behalf?

Thanks.