VBA search for characters+hard return

[Dabenic]

Hello all!

I have a series of cells I need to work on and separate the contents based on criteria. The common characteristic marking the point where I need to separate text is two commas followed by a hard return.

How can I format the search criteria so it looks for ",," plus the hard return (vbLf)?


Thank you!

 

[kweaver]

In your example, is there a hard return after sample4 and the 3 commas?

 

[Micron]

In your example, is there a hard return after sample4 and the 3 commas?

Good eye!

 

[kweaver]

Without resorting to VBA, this might do it:

Book2
	A
2	sample1,sample2,,sample4,,, sample7,,sample8,, extract1,extract2,extract4,,,,,,extract10,, data1,data3,data4
3
4	sample1,sample2,,sample4,,, sample7,,sample8,,
5	extract1,extract2,extract4,,,,,,extract10,,
6	data1,data3,data4
Sheet3

Cell Formulas
Range	Formula
A4	A4	=LEFT(A2,3+FIND(",,"&CHAR(10),SUBSTITUTE(A2,",,,","~")))
A5	A5	=MID(A2,1+LEN(A4),1+FIND(",,"&CHAR(10),SUBSTITUTE(RIGHT(A2,LEN(A2)-LEN(A4)),",,,","~",LEN(A2)+1)))
A6	A6	=RIGHT(A2,LEN(A2)-(LEN(A4)+LEN(A5)+1))

 

[Dabenic]

Welcome to Mr. Excel.

It might help if you had examples of your data and expected results.

Do you mean this:

=LEFT(A2,FIND(",,"&CHAR(10),A2)-1)

and

=RIGHT(A2,LEN(A2)-FIND(",,"&CHAR(10),A2)-1)

with your text in A2 ??

These might work. I will spend some more time working with them.

 

[Dabenic]

Thank you kweaver and Micron for your help! I was able to take both of your inputs into consideration. I had found a Split function but couldn't get it to work until now. Below is what I have that works.

Sub CommaCommaHR_Split()
  
Dim sample As String
Dim EX As Integer
Dim extract() As String
  
  sample = Range("B6")
  extract = Split(sample, ",," & Chr(13))

  For EX = 0 To UBound(extract)
  
        Cells(EX + 6, 2).Value = extract(EX)
  
  Next EX
    

End Sub

 

[kweaver]

With only ONE example, it's difficult (impossible) to write code that will work in all of your cases. Please provide add'l examples.