Prev: keystroke shortcut needed
Next: Bankup of worksheet
From: Randy on 27 May 2010 16:17
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
From: Randy on 27 May 2010 16:47
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
From: Jacob Skaria on 27 May 2010 17:14
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
From: Jacob Skaria on 27 May 2010 17:38
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
 | 
Pages: 1
Prev: keystroke shortcut needed
Next: Bankup of worksheet