I have Excel 2003. So I got a long list of movies and its mixed with Foreign and American names. Instead of going one by one, is it possible to select all and delete the foreign at once? Also, once you delete it, it leaves gap in between, so how do I delete the gaps instead of again deleting one by one? What about if I want to separate it? Try putting these in your Excel spread sheet and see if you can find a easy solution. Thank you.
Here is a sample how the movies are listed in Excel Spreadsheet. I want to remove the foreign ones and keep the American names.
100 Days With Mr. Arogant (2004) [Korean]
10th & Wolf (2006)
11th Mother (2007) [Korean]
13 Beloved (2006) [Thailand]
16 Blocks (2006)
2 Young (2005) [Hong Kong]
200 Pounds Beauty (Korean)
21 - The Movie
21 Grams
3:10 To Yuma
3-Iron (Korean)
40 Year Old Virgin
9 Souls (Japanese)
A Bittersweet Life (Korean)
A Bloody Aria 2006 (Korean)
A Bridge Too Far (1977)
A Bronx Tale
A Christmas Story (1983)
A Day (2001) [Korean]
A Dirty Carnival (Korean)
A Family/Gajok (Korean)
A Fighters Blues (Cantonese/Hong Kong)
A Fond Kiss
A Frozen Flower (2008) [Korean]
A Good Day To Have An Affair (2007) [Korean]
A Good Lawyer's Wife (2003) [Korean]
A Great Chinese Restaurant (1999) [Korean]
A Guide To Recognizing Your Saints
A Hero Never Dies (1998) [Hong Kong]
A History Of Violence
A Little Monk (2002) [Korean]
A Love (Sarang) (Korean)
A Love Song For Bobby Long
A Man Who Was Superman (Korean)
A Million (2009) [Korean]
A Millionaires First Love (Korean)
A Moment To Remember (Korean)
A Perfect World (1993)
A Petal (Korean)
A Simple Plan
A Time To Kill
A Very Long Engagement (2004) [French]
A War Name Desire (2000) [Hong Kong]
A Wednesday [India/Hindi]
Aaja Nachle [India/Hindi]
Aamir [India/Hindi]
About Love (2005) [Japanese]
Accepted
Achilles And The Tortoise (2008) [Japanese]
Address Unknown (Korean)
After This Our Exile - Directors Cut (2006) [Hong Kong]
Aiki (Japanese)
Akarui Mirai/Bright Future (Japanese)
Akeelah and The Bee
Ali
Aliens (Special Edition) 86'
All Around Us (2008) [Japanese]
Almost Famous
Amal [India/Hindi]
American Beauty
American Gangster (Unrated Edition)
American History X
AmericanEast
Americano
Amistad
Amores Perros - Loves A ***** (Spanish)
Amreeka (2009) [Arabic]
Angela's Ashes
Angels of the Sun (Portuguese)
Apocalypto (Mayan)
Around The World In 80 Days
Art School Confidential
As Good As It Gets
Assembly/Ji Jie Hao (Chinese/Mandarin)
Austin Powers: The Spy Who Shagged Me
Aviator
Update:garbo7441......This new code works pretty good. Please also post if I wanted to separate it. Thank you so much. :)
Answers & Comments
Verified answer
Edit 2: Macro to separate the movies follows:
Sub Separate_Foreign()
Dim i, j, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 1 Step -1
If InStr(Cells(i, "A"), "[") Then
Cells(i, "A").EntireRow.Copy Destination:=Sheets("Sheet2"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Cells(i, "A").EntireRow.Delete
End If
Next i
For i = LastRow To 1 Step -1
For j = 1 To Len(Cells(i, "A"))
If Mid(Cells(i, "A"), j, 1) = "(" Then
If Not IsNumeric(Mid(Cells(i, "A"), j + 1, 1)) Then
Cells(i, "A").EntireRow.Copy Destination:=Sheets("Sheet2"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Cells(i, "A").EntireRow.Delete
End If
End If
Next j
Next i
Sheets("Sheet2").Activate
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub
'===========
Edit: Revised macro follows...
Copy this macro to the clipboard:
Sub Delete_Foreign()
Dim i, j, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 1 Step -1
If InStr(Cells(i, "A"), "[") Then
Cells(i, "A").EntireRow.Delete
End If
Next i
For i = LastRow To 1 Step -1
For j = 1 To Len(Cells(i, "A"))
If Mid(Cells(i, "A"), j, 1) = "(" Then
If Not IsNumeric(Mid(Cells(i, "A"), j + 1, 1)) Then
Cells(i, "A").EntireRow.Delete
End If
End If
Next j
Next i
End Sub
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the macro into the editing area to the right.
Still in the VBE, go to Tools > Macros, select the macro, and click 'RUN'.
Close the VBE.
Answer beneath most effective releavnt if you're making use of Excel 2007 Remove reproduction values When you do away with reproduction values, most effective the values within the variety of cells or desk are affected. Any different values external the variety of cells or desk aren't altered or moved. Caution Because you're completely deleting knowledge, it is a well notion to replicate the long-established variety of cells or desk to yet another worksheet or workbook earlier than taking away reproduction values. Select the variety of cells, or ensure that the lively mobilephone is in a desk. On the Data tab, within the Data Tools institution, click on Remove Duplicates. Do a number of of the next: Under Columns, decide upon a number of columns. To speedily decide upon all columns, click on Select All. To speedily transparent all columns, click on Unselect All. If the variety of cells or desk involves many columns and also you wish to simply decide upon a couple of columns, you may also discover it less complicated to click on Unselect All, after which beneath Columns, decide upon the ones columns. Click OK. A message is displayed indicating what number of reproduction values had been eliminated and what number of exact values stay, or if no reproduction values had been eliminated. Click OK.
It would help if all the country names were in square brackets so you could just search for a square bracket and delete that row, using a macro.