Find
 

I have numbers in column A that don't belong such as;
74-350-77-50
18-350-077-51
36-100-001-03

the 3rd digit should not be a "-"
the rest of them would read
74350-77-50
18350-077-51
36100-001-03

What I need to do is find them and delete that row

Any ideas?
--
Randy

Find
 

Thank you so much, you're a life saver
--
Randy


"Jacob Skaria" wrote:

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Mid(Range("A" & lngRow).Text, 3, 1) = "-" Then Rows(lngRow).Delete
Next
End Sub


PS: If you dont prefer a VBA solution in cell B1 enter the formula
=MID(A1,3,1)="-" and copy down as required. Sort ColB and delete rows with
TRUE

--
Jacob (MVP - Excel)


"Randy" wrote:

Thanks for getting back so soon, however I don't want to replace the dash, I
want to delete any rows where the 3rd character = "-"
--
Randy


"Jacob Skaria" wrote:

Try SUBSTITUTE() with the text in cell A1

=SUBSTITUTE(A1,"-",,1)

--
Jacob (MVP - Excel)


"Randy" wrote:

I have numbers in column A that don't belong such as;
74-350-77-50
18-350-077-51
36-100-001-03

the 3rd digit should not be a "-"
the rest of them would read
74350-77-50
18350-077-51
36100-001-03

What I need to do is find them and delete that row

Any ideas?
--
Randy

Find
 

Try SUBSTITUTE() with the text in cell A1

=SUBSTITUTE(A1,"-",,1)

--
Jacob (MVP - Excel)


"Randy" wrote:

I have numbers in column A that don't belong such as;
74-350-77-50
18-350-077-51
36-100-001-03

the 3rd digit should not be a "-"
the rest of them would read
74350-77-50
18350-077-51
36100-001-03

What I need to do is find them and delete that row

Any ideas?
--
Randy

Find
 

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Mid(Range("A" & lngRow).Text, 3, 1) = "-" Then Rows(lngRow).Delete
Next
End Sub


PS: If you dont prefer a VBA solution in cell B1 enter the formula
=MID(A1,3,1)="-" and copy down as required. Sort ColB and delete rows with
TRUE

--
Jacob (MVP - Excel)


"Randy" wrote:

Thanks for getting back so soon, however I don't want to replace the dash, I
want to delete any rows where the 3rd character = "-"
--
Randy


"Jacob Skaria" wrote:

Try SUBSTITUTE() with the text in cell A1

=SUBSTITUTE(A1,"-",,1)

--
Jacob (MVP - Excel)


"Randy" wrote:

I have numbers in column A that don't belong such as;
74-350-77-50
18-350-077-51
36-100-001-03

the 3rd digit should not be a "-"
the rest of them would read
74350-77-50
18350-077-51
36100-001-03

What I need to do is find them and delete that row

Any ideas?
--
Randy