Extracting cells containing certain characters

[MikeH93]

I have a lot of data in cells but I am really only interested in certain cells. I want to extract the cell in each row which contains a "-" (there is one in each row) and move them into a column of their own. The entries containing the "-" are not adjacent which makes it tricky to move all of them. I can select all the cells using the find function but then I can't copy all selected cells and as far as I am aware none of the FIND or SEARCH functions can help me.

What I have:

1	2	3	4	5
27	45	36-X	23	15
34	56	23	34	67-Y
19	54-P	45	12	67

<tbody>
</tbody>

What I want to get:

1	2	3	4	5	Important Cells
27	45	36-X	23	15	36-X
34	56	23	34	67-Y	67-Y
19	54-P	45	12	67	54-P

<tbody>
</tbody>

Given the range of tasks I've been able to perform using simple tricks and formulas, I thought this would be easy enough but having searched around, I still haven't found a solution. Would I have to look into using VBA or is there an intuitive way of getting this done? My VBA skills are very limited. Any help on the matter would be greatly appreciated.

 

[cbatrody]

Hi,

Is it possible that there can be more than one cells with "-" in a row? In the above example you have one "-" cells for each row.

 

[cbatrody]

Hi,

If there is only one cell with "-" in each row, try the following formula in F1:

=IF(ISERROR(SEARCH("-",A1)),IF(ISERROR(SEARCH("-",B1)),IF(ISERROR(SEARCH("-",C1)),IF(ISERROR(SEARCH("-",D1)),IF(ISERROR(SEARCH("-",E1)),"",E1),D1),C1),B1),A1)

drag this down the cells.

This is keeping in mind that you have the data in columns A:F

 

[MikeH93]

Hi cbatrody. No there will only ever be one cell with one "-" in it for each row, just like the above scenario.

 

[Rick Rothstein]

Give this macro a try...

Sub ExtractDashedCells()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    Rows(R).Find("-", , , xlPart).Copy Cells(R, "F")
  Next
  Application.ScreenUpdating = True
End Sub

 

[MikeH93]

That worked perfectly. Thank you so much cbatrody, much appreciated.

 

[Rick Rothstein]

That worked perfectly. Thank you so much cbatrody, much appreciated.

Sorry about my macro posting, I misread (by reading too fast) your original post as seeking one. Formula-wise, there is a simpler one available...

=INDEX(A2:E2,1,MATCH("*-*",A2:E2,0))

 

[My Aswer Is This]

I guess I'm now late to this post. Here was my script.

Sub TestMe()
Application.ScreenUpdating = False
Dim i As Integer
Dim b As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        For b = 1 To 5
            If InStr(Cells(i, b).Value, "-") Then
                Cells(i, 6).Value = Cells(i, b).Value
                b = b + 1
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub