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

darin100K

Board Regular
Joined
May 17, 2005
Messages
97
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
try this macro


Code:
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

Board Regular
Joined
May 17, 2005
Messages
97
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Select the cell first and try
Code:
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

Board Regular
Joined
May 17, 2005
Messages
97
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

try
Code:
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

Board Regular
Joined
May 17, 2005
Messages
97
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OOPs!
Can you just change one line that is
Code:
.Offset(1).Resize(UBound(x) + 1).Value = x
To
Code:
.Offset(1).Resize(UBound(x) + 1).Value = Application.Transpose(x)
 

darin100K

Board Regular
Joined
May 17, 2005
Messages
97
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.
 

Forum statistics

Threads
1,141,587
Messages
5,707,238
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top