• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Delete Cell with Specific Word/Value

F

Fahad Malik

New Member
Hi,
This is my First Thread, and you guys are doing great job.
I want to delete the cell(not entire row or column), which contain the specific word.

I am using windows(excel 2007) as well as mac operating system. I have a sheet of 25k+ rows which has redundant value and link. The whole row is good, column is good, but it is not uploading on product as csv just because of some value is not assigned correctly. Like a 2k row has column value of image URL with
"www.example.com/abc def.jpg"
"www.example.com/xyz def.jpg"
"www.example.com/jkl def.jpg"
................... and so on

You can see that there is a gap(space) in URL so its not loading the entire row as it shows error.
I just want to search "def.jpg" and delete the entire cell "www.example.com/abc def.jpg" and repeat for all def.jpg for 2k rows. If that cell will be cleared, thenit can be uploaded.

Line 81-85: Validation failed: http://example.com/image/products/Womens Wear2.jpg is not a valid URL

You can see, the example error.
Row 81-85 can not be created just because of that last word "Womens Wear2.jpg"
If i clear that cell, then it can be uploaded.

I just want to delete the cell which contain the specific value/word for the entire workbook.
 
Fahad

Firstly, Welcome to the Chandoo.org Forums

This question has been asked several times so maybe try using the search Box at the top Right of this screen

Manually you can do the following

Select the range
Ctrl F
Search For: def.jpg
Find All
Select 1 of the items in the bottom pane
Ctrl A - This will select all the cells with def.jpg
Esc - leave the dialog, cells are still selected
Delete - will delete the cells contents
 
If the DEF is random letters and Hui's suggestion does not work then this. Put this in a junk column

=FIND(" ",A2,1)

Where your hyperlinks are in Col A. Drag the formula down to the bottom of the range. Now Filter by that column, and uncheck the

#Value!

You will be left with all the dud items in the list. Just clearcontents while the filter is on and you are done.

Take care

Smallman
 
Thanks HUI, You saved my several hundred Hours :P
Thanks for the fast reply. Thanks a Lot once Again.

Thanks SmallMan too, yet i didnt tried your method, but thanks for your new method also.
May be i have to use your method also in another excel sheet.

Thanks HUI and SmallMan. Have a Great Day Ahead.
 
You must log in or register to reply here.
Back
Top