Delete Cells in column

[omegadeltaphi]

Please Help

I have an excel file where i have unnecessary information. So I would like to delete all the cells I dont need. They are all similiar in that they are (3 characters) long. ex................. (B!T, DVA, M2A, N&T) just like that. All of them are three chars. The rest of my data in other fields is much longer so maybe i can use come kind of condition <3 something or rather???????????????


Im not proficient in code. I have been digging all day to no avail.

Please help, Thanks in Advance

Steve

 

[Trebor76]

Hi Steve,

Welcome to MrExcel!!

Couple of initial questions:

Which column(s) contains the data and what row does the data start from?

Robert

 

[omegadeltaphi]

It is just one column (a) and thousands of rows (3000). Most of the rows in the column have useful data but 20% of it is useless.

I hope that answers your question?

Thanks Robert

Steve

 

[Trebor76]

Though you didn't answer what row the starts from, try this (which starts from Row 2 - change to suit):

Sub Macro1()

    Dim rngCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Len(rngCell.Value) = 3 Then
            rngCell.ClearContents
        End If
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Note too that as the code will be clearing data, make sure to initially run it on a copy of your data in case the results are not as expected.

Regards,

Robert

 

[omegadeltaphi]

ex:

useful Address
useful #200400600
vb2
Useful Adress
useful acct#
B!9
Useful Address: 207 Pine st
#985589302
DBK

Steve

 

[omegadeltaphi]

thanks so much Robert
Weeeeeeeeeeeeeeeeeeeeeeeeeeeee!!!!!!



Steve

 

[Rick Rothstein]

Here is a short and fast macro to do what you asked...

Sub ClearThreeCharacterCells()
  Columns("A").Replace "???", "", xlWhole
End Sub

Just to point out, though, that you can do this with Excel's built-in Replace functionality. Select Column A and then press CTRL+H to bring up the Replace dialog box. Type ??? in the "Find what" field, make sure the "Replace with" field is empty. Click the "Options>>" button in order to see all the options available to you (assuming they are not already displayed). Make sure the "Match entire cell contents" checkbox has a check mark in it, then click the "Replace All" button.

 

[John Davis]

Here is a short and fast macro to do what you asked...

Sub ClearThreeCharacterCells()
  Columns("A").Replace "???", "", xlWhole
End Sub

Just to point out, though, that you can do this with Excel's built-in Replace functionality. Select Column A and then press CTRL+H to bring up the Replace dialog box. Type ??? in the "Find what" field, make sure the "Replace with" field is empty. Click the "Options>>" button in order to see all the options available to you (assuming they are not already displayed). Make sure the "Match entire cell contents" checkbox has a check mark in it, then click the "Replace All" button.

Nice. I like that one. Good to know.

 

[Rick Rothstein]

Hey John, I see you are from New Jersey also. Anywhere near central NJ by any chance? You can answer me via email so we don't clutter up this thread with an off-topic subthread.

rickDOTnewsATverizonDOTnet

just replace the upper case letters with the symbols they spell out.