If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|