This follows from this thread
There is another problem with the same dataset which I would like to solve. Please let me know if I should make another thread as this is a separate problem. Minisheet attached.
In the release year column I would like all dates to be formatted as DD/MM/YYYY. There are some dates in there that requiring fixing such as
"23 December of 1996", "09 21 1972", "01/16-03". Instead of manually converting these to DD/MM/YYY, how can I utilise excel tools to this for me?
Similarly, in the country column, some countries are misspelled such as "New Zesland" instead of "New Zealand", "US." instead of "USA" and "Italy1" instead of "Italy". I was able to identify these misspellings by eye as the dataset is small, however, if there were hundreds of thousands of rows it would have been near impossible to identify them all. How should I go abouts in identifying and fixing these spelling errors using the excel tools?
Would appreciate any assistance here
Just to add, I found an article which suggested the following Macro to identify misspellings. After running it all the above misspellings were highlighted, however "Italy1" was not.
Sub ColorMispelledCells()
For Each cl In ActiveSheet.UsedRange
If Not Application.CheckSpelling(Word:=cl.Text) Then _
cl.Interior.ColorIndex = 28
Next cl
End Sub
There is another problem with the same dataset which I would like to solve. Please let me know if I should make another thread as this is a separate problem. Minisheet attached.
In the release year column I would like all dates to be formatted as DD/MM/YYYY. There are some dates in there that requiring fixing such as
"23 December of 1996", "09 21 1972", "01/16-03". Instead of manually converting these to DD/MM/YYY, how can I utilise excel tools to this for me?
Similarly, in the country column, some countries are misspelled such as "New Zesland" instead of "New Zealand", "US." instead of "USA" and "Italy1" instead of "Italy". I was able to identify these misspellings by eye as the dataset is small, however, if there were hundreds of thousands of rows it would have been near impossible to identify them all. How should I go abouts in identifying and fixing these spelling errors using the excel tools?
Would appreciate any assistance here
Just to add, I found an article which suggested the following Macro to identify misspellings. After running it all the above misspellings were highlighted, however "Italy1" was not.
Sub ColorMispelledCells()
For Each cl In ActiveSheet.UsedRange
If Not Application.CheckSpelling(Word:=cl.Text) Then _
cl.Interior.ColorIndex = 28
Next cl
End Sub
| IMDB_Data_Analysis.csv | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Original Title | Release Year | Genre | Duration | Country | Content Rating | Director | Gross Worldwide | Votes | Score | ||
| 2 | The Shawshank Redemption | 10/02/1995 | Drama | 142.00 | USA | R | Frank Darabont | $28,815,245.00 | 2.278.845 | 9.30 | ||
| 3 | The Godfather | 09 21 1972 | Crime Drama | 175.00 | USA | R | Francis Ford Coppola | $246,120,974.00 | 1.572.674 | 9.20 | ||
| 4 | The Dark Knight | 23 -07-2008 | Action Crime Drama | 152.00 | US | PG-13 | Christopher Nolan | $1,005,455,211.00 | 2.241.615 | 9.00 | ||
| 5 | The Godfather: Part II | 25/09/1975 | Crime Drama | 220.00 | USA | R | Francis Ford Coppola | $ 4o835783 | 1.098.714 | 90.00 | ||
| 6 | Pulp Fiction | 28/10/1994 | Crime Drama | 154.00 | USA | R | Quentin Tarantino | $222,831,817.00 | 1.780.147 | 89f | ||
| 7 | The Lord of the Rings: The Return of the King | 22/02/2004 | Action Adventure Drama | 201.00 | New Zealand | PG-13 | Peter Jackson | $1,142,271,098.00 | 1.604.280 | 8.90 | ||
| 8 | Schindler's List | 11/03/1994 | Biography Drama History | 195.00 | USA | R | Steven Spielberg | $322,287,794.00 | 1.183.248 | 8.90 | ||
| 9 | 12 Angry Men | 4/09/1957 | Crime Drama | 96.00 | USA | PG | Sidney Lumet | $576.00 | 668.47 | 8.90 | ||
| 10 | Inception | 24/09/2010 | Action Adventure Sci-Fi | 148.00 | USA | PG-13 | Christopher Nolan | $869,784,991.00 | 2.002.816 | 8..8 | ||
| 11 | Fight Club | 10-29-99 | Drama | 139.00 | UK | R | David Fincher | $101,218,804.00 | 1.807.440 | 8.80 | ||
| 12 | Forrest Gump | 6/10/1994 | Drama Romance | 142.00 | USA | PG-13 | Robert Zemeckis | $678,229,452.00 | 1.755.490 | 0.34 | ||
| 13 | The Lord of the Rings: The Fellowship of the Ring | 18/01/2002 | Action Adventure Drama | 178.00 | New Zesland | PG-13 | Peter Jackson | $887,934,303.00 | 1.619.920 | 8.80 | ||
| 14 | Il buono il brutto il cattivo | 23rd December of 1966 | Western | 161.00 | Italy | R | Sergio Leone | $25,252,481.00 | 672.50 | 8.80 | ||
| 15 | The Matrix | 7/05/1999 | Action Sci-Fi | 136.00 | USA | R | Lana Wachowski Lilly Wachowski | $465,718,588.00 | 1.632.315 | 8.70 | ||
| 16 | The Lord of the Rings: The Two Towers | 01/16-03 | Action Adventure Drama | 179.00 | New Zeland | PG-13 | Peter Jackson | $951,227,416.00 | 1.449.778 | 8.7. | ||
| 17 | Star Wars: Episode V - The Empire Strikes Back | 19/09/1980 | Action Adventure Fantasy | 124.00 | USA | PG | Irvin Kershner | $549,265,501.00 | 1.132.073 | 87.00 | ||
| 18 | Goodfellas | 20/09/1990 | Biography Crime Drama | 146.00 | USA | R | Martin Scorsese | $46,879,633.00 | 991.51 | 8.70 | ||
| 19 | One Flew Over the Cuckoo's Nest | 18/11/1976 | Drama | 133.00 | USA | R | Milos Forman | $108,997,629.00 | 891.07 | 8.70 | ||
| 20 | Interstellar | 6/11/2014 | Adventure Drama Sci-Fi | 169.00 | USA | PG-13 | Christopher Nolan | $696,742,056.00 | 1.449.256 | 8.60 | ||
| 21 | Se7en | 15/12/1995 | Crime Drama Mystery | 127.00 | USA | R | David Fincher | $327,333,559.00 | 1.402.015 | 8.60 | ||
| 22 | The Silence of the Lambs | 5/03/1991 | Crime Drama Thriller | 118.00 | USA | R | Jonathan Demme | $272,753,884.00 | 1.234.134 | 86.00 | ||
| 23 | Star Wars | 20/10/1977 | Action Adventure Fantasy | 121.00 | USA | PG | George Lucas | $775,768,912.00 | 1.204.107 | 8.60 | ||
| 24 | Saving Private Ryan | 30/10/1998 | Drama War | 169.00 | USA | R | Steven Spielberg | $482,349,603.00 | 1.203.825 | 8.60 | ||
| 25 | The Green Mile | 3/10/2000 | Crime Drama Fantasy | 189.00 | US. | R | Frank Darabont | $286,801,374.00 | 1.112.336 | 8.60 | ||
| 26 | Cidade de Deus | 9/05/2003 | Crime Drama | 130.00 | Brazil | R | Fernando Meirelles Kátia Lund | $30,680,793.00 | 685.86 | 8.60 | ||
| 27 | Sen to Chihiro no kamikakushi | 18/04/2003 | Animation Adventure Family | 125.00 | Japan | PG | Hayao Miyazaki | $355,467,056.00 | 626.69 | 8.60 | ||
| 28 | La vita è bella | 20/12/1997 | Comedy Drama Romance | 116.00 | Italy1 | PG-13 | Roberto Benigni | $230,098,753.00 | 605.65 | 8.60 | ||
| 29 | Gisaengchung | 7/11/2019 | Comedy Drama Thriller | 132.00 | South Korea | R | Bong Joon Ho | $257,604,912.00 | 470.93 | 8.60 | ||
| 30 | It's a Wonderful Life | 11/03/1948 | Drama Family Fantasy | 130.00 | USA | PG | Frank Capra | $6,130,720.00 | 388.31 | 8.60 | ||
| 31 | Shichinin no samurai | 19/08/1955 | Action Adventure Drama | 207.00 | Japan | PG-13 | Akira Kurosawa | $322,773.00 | 307.96 | 8.60 | ||
| 32 | Gladiator | 19/05/2000 | Action Adventure Drama | 155.00 | USA | R | Ridley Scott | $465,361,176.00 | 1.308.193 | 8.50 | ||
| 33 | The Departed | 27/10/2006 | Crime Drama Thriller | 151.00 | USA | R | Martin Scorsese | $291,465,034.00 | 1.159.703 | 8.50 | ||
| 34 | The Prestige | 22/12/2006 | Drama Mystery Sci-Fi | 130.00 | UK | PG-13 | Christopher Nolan | $109,676,311.00 | 1.155.723 | 8.50 | ||
| 35 | Back to the Future | 18/10/1985 | Adventure Comedy Sci-Fi | 116.00 | USA | PG | Robert Zemeckis | $388,774,684.00 | 1.027.330 | 8.50 | ||
| 36 | American History X | 27/08/1999 | Drama | 119.00 | USA | R | Tony Kaye | $23,875,127.00 | 1.014.218 | 8.50 | ||
| 37 | Leon | 7/04/1995 | Action Crime Drama | 110.00 | France | R | Luc Besson | $19,552,639.00 | 1.007.598 | 8.50 | ||
| 38 | Terminator 2: Judgment Day | 19/12/1991 | Action Sci-Fi | 137.00 | USA | R | James Cameron | $520,884,847.00 | 974.97 | 8.40 | ||
| 39 | The Usual Suspects | 30/11/1995 | Crime Mystery Thriller | 106.00 | USA | R | Bryan Singer | $23,341,568.00 | 968.95 | 8.40 | ||
| 40 | The Lion King | 25/11/1994 | Animation Adventure Drama | 88.00 | USA | G | Roger Allers Rob Minkoff | $968,511,805.00 | 917.25 | 8.40 | ||
| 41 | Joker | 3/10/2019 | Crime Drama Thriller | 122.00 | USA | R | Todd Phillips | $1,074,251,311.00 | 855.10 | 8.40 | ||
| 42 | Intouchables | 24/02/2012 | Biography Comedy Drama | 112.00 | France | R | Olivier Nakache Éric Toledano | $426,588,510.00 | 736.69 | 8.40 | ||
| 43 | The Pianist | 25/10/2002 | Biography Drama Music | 150.00 | UK | R | Roman Polanski | $120,072,577.00 | 707.94 | 8.40 | ||
| 44 | Whiplash | 12/02/2015 | Drama Music | 106.00 | USA | R | Damien Chazelle | $48,983,260.00 | 690.73 | 8.40 | ||
| 45 | Psycho | 28/10/1960 | Horror Mystery Thriller | 109.00 | USA | R | Alfred Hitchcock | $32,008,644.00 | 586.77 | 8.30 | ||
| 46 | Casablanca | 21/11/1946 | Drama Romance War | 102.00 | USA | PG | Michael Curtiz | $4,374,761.00 | 509.95 | 8.30 | ||
| 47 | C'era una volta il West | 21/12/1968 | Western | 165.00 | Italy | PG-13 | Sergio Leone | $112,911.00 | 295.22 | 8.30 | ||
| 48 | Hotaru no haka | 11/10/2015 | Animation Drama War | 89.00 | Japan | PG | Isao Takahata | $516,962.00 | 225.44 | 8.30 | ||
| 49 | Nuovo Cinema Paradiso | 17/11/1988 | Drama | 155.00 | Italy | PG | Giuseppe Tornatore | $13,826,605.00 | 223.05 | 8.30 | ||
| 50 | Modern Times | 12/03/1937 | Comedy Drama Family | 87.00 | USA | G | Charles Chaplin | $457,688.00 | 211.25 | 8.30 | ||
| 51 | The Dark Knight Rises | 29/08/2012 | Action Adventure | 164.00 | UK | PG-13 | Christopher Nolan | $1,081,133,191.00 | 1.480.582 | 8.30 | ||
| 52 | Django Unchained | 17/01/2013 | Drama Western | 165.00 | USA | R | Quentin Tarantino | $425,368,238.00 | 1.317.856 | 8.30 | ||
| 53 | Memento | 19/01/2001 | Mystery Thriller | 113.00 | USA | R | Christopher Nolan | $39,970,386.00 | 1.098.879 | 8.20 | ||
| 54 | WALL·E | 17/10/2008 | Animation Adventure Family | 98.00 | USA | G | Andrew Stanton | $521,311,860.00 | 974.73 | 8.20 | ||
| 55 | The Shining | 22/12/1980 | Drama Horror | 146.00 | UK | R | Stanley Kubrick | $46,520,613.00 | 869.48 | 8.20 | ||
| 56 | Raiders of the Lost Ark | 12/06/1981 | Action Adventure | 115.00 | USA | PG | Steven Spielberg | $390,133,212.00 | 865.51 | 8.20 | ||
| 57 | Avengers: Infinity War | 25/04/2018 | Action Adventure Sci-Fi | 149.00 | USA | PG-13 | Anthony Russo Joe Russo | $2,048,359,754.00 | 796.49 | 8.20 | ||
| 58 | Alien | 25/10/1979 | Horror Sci-Fi | 117.00 | UK | R | Ridley Scott | $108,110,316.00 | 768.87 | 8.20 | ||
| 59 | Avengers: Endgame | 24/04/2019 | Action Adventure Drama | 181.00 | USA | PG-13 | Anthony Russo Joe Russo | $2,797,800,564.00 | 754.79 | 8.20 | ||
| 60 | Apocalypse Now | 18/12/1979 | Drama Mystery War | 147.00 | USA | R | Francis Ford Coppola | $91,968,688.00 | 591.25 | 8.20 | ||
| 61 | Oldeuboi | 6/05/2005 | Action Drama Mystery | 120.00 | South Korea | R | Chan-wook Park | $15,002,116.00 | 501.08 | 8.10 | ||
| 62 | Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb | 3/04/1964 | Comedy | 95.00 | UK | PG | Stanley Kubrick | $9,443,876.00 | 441.12 | 8.10 | ||
| 63 | Rear Window | 14/04/1955 | Mystery Thriller | 112.00 | USA | PG | Alfred Hitchcock | $37,032,034.00 | 432.39 | 8.10 | ||
| 64 | Coco | 28/12/2017 | Animation Adventure Family | 105.00 | USA | PG | Lee Unkrich Adrian Molina | $807,083,670.00 | 352.46 | 8.10 | ||
| 65 | Das Leben der Anderen | 6/04/2007 | Drama Mystery Thriller | 137.00 | Germany | R | Florian Henckel von Donnersmarck | $77,356,942.00 | 349.64 | 8.10 | ||
| 66 | Spider-Man: Into the Spider-Verse | 25/12/2018 | Animation Action Adventure | 117.00 | USA | PG | Bob Persichetti Peter Ramsey | $375,540,831.00 | 335.89 | 8.10 | ||
| 67 | 3 Idiots | 25/12/2009 | Comedy Drama | 170.00 | India | PG-13 | Rajkumar Hirani | $60,262,836.00 | 332.22 | 8.10 | ||
| 68 | Mononoke-hime | 19/05/2000 | Animation Adventure Fantasy | 134.00 | Japan | PG-13 | Hayao Miyazaki | $169,785,629.00 | 331.05 | 8.00 | ||
| 69 | Once Upon a Time in America | 28/09/1984 | Crime Drama | 229.00 | USA | R | Sergio Leone | $5,472,914.00 | 302.32 | 8.00 | ||
| 70 | The Great Dictator | 25/06/1945 | Comedy Drama War | 125.00 | USA | PG | Charles Chaplin | $969,879.00 | 197.38 | 8.00 | ||
| 71 | Sunset Blvd. | The 6th of marzo year 1951 | Drama Film-Noir | 110.00 | USA | PG | Billy Wilder | $299,645.00 | 195.79 | 8.00 | ||
| 72 | Inglourious Basterds | 2/10/2009 | Adventure Drama War | 153.00 | Germany | R | Quentin Tarantino | $321,455,689.00 | 1.229.958 | 8.00 | ||
| 73 | American Beauty | 21/01/2000 | Drama | 122.00 | USA | R | Sam Mendes | $356,296,601.00 | 1.049.009 | 8.00 | ||
| 74 | Braveheart | 1/12/1995 | Biography Drama History | 178.00 | USA | R | Mel Gibson | $213,216,216.00 | 941.68 | 7.90 | ||
| 75 | Star Wars: Episode VI - Return of the Jedi | 21/10/1983 | Action Adventure Fantasy | 131.00 | USA | PG | Richard Marquand | $475,347,111.00 | 928.04 | 7.90 | ||
| 76 | Reservoir Dogs | 9/10/1992 | Crime Drama Thriller | 99.00 | USA | R | Quentin Tarantino | $2,889,963.00 | 896.55 | 7.90 | ||
| 77 | Eternal Sunshine of the Spotless Mind | 22/10/2004 | Drama Romance Sci-Fi | 108.00 | USA | R | Michel Gondry | $74,036,715.00 | 889.88 | 7.90 | ||
| 78 | Toy Story | 22/03/1996 | Animation Adventure Comedy | 81.00 | USA | G | John Lasseter | $404,265,438.00 | 864.46 | 7.90 | ||
| 79 | Good Will Hunting | 6/03/1998 | Drama Romance | 126.00 | USA | R | Gus Van Sant | $225,933,435.00 | 837.38 | 7.80 | ||
| 80 | Snatch | 16/03/2001 | Comedy Crime | 104.00 | UK | R | Guy Ritchie | $83,557,872.00 | 766.59 | 7.80 | ||
| 81 | Requiem for a Dream | 15/12/2000 | Drama | 102.00 | USA | R | Darren Aronofsky | $7,390,108.00 | 748.29 | 7.80 | ||
| 82 | A Clockwork Orange | 7/09/1972 | Crime Drama Sci-Fi | 136.00 | UK | R | Stanley Kubrick | $26,903,440.00 | 740.30 | 7.80 | ||
| 83 | Toy Story 3 | 7/07/2010 | Animation Adventure Comedy | 103.00 | USA | G | Lee Unkrich | $1,066,969,703.00 | 739.72 | 7.80 | ||
| 84 | Scarface | 1984-02-34 | Crime Drama | 170.00 | USA | R | Brian De Palma | $66,023,585.00 | 721.34 | 7.80 | ||
| 85 | Taxi Driver | 1976-13-24 | Crime Drama | 114.00 | USA | R | Martin Scorsese | $28,441,292.00 | 703.26 | 7.70 | ||
| 86 | Le fabuleux destin d'Amélie Poulain | 25/01/2002 | Comedy Romance | 122.00 | France | R | Jean-Pierre Jeunet | $173,924,742.00 | 690.48 | 7.70 | ||
| 87 | Full Metal Jacket | 9/10/1987 | Drama War | 116.00 | UK | R | Stanley Kubrick | $46,357,676.00 | 658.18 | 7.70 | ||
| 88 | Aliens | 24/09/1986 | Action Adventure Sci-Fi | 137.00 | USA | R | James Cameron | $131,384,634.00 | 639.50 | 7.70 | ||
| 89 | 2001: A Space Odyssey | 12/12/1968 | Adventure Sci-Fi | 149.00 | UK | G | Stanley Kubrick | $68,989,547.00 | 587.87 | 7.60 | ||
| 90 | Citizen Kane | 25/11/1948 | Drama Mystery | 119.00 | USA | PG | Orson Welles | $1,594,107.00 | 389.32 | 7.60 | ||
| 91 | 1917 | 23/01/2020 | Drama War | 119.00 | USA | R | Sam Mendes | $384,857,224.00 | 362.90 | 7.60 | ||
| 92 | Amadeus | 15/02/1985 | Biography Drama History | 160.00 | USA | R | Milos Forman | $52,066,791.00 | 361.03 | 7.60 | ||
| 93 | Vertigo | 18/11/1958 | Mystery Romance Thriller | 128.00 | USA | PG | Alfred Hitchcock | $7,796,389.00 | 352.79 | 7.50 | ||
| 94 | North by Northwest | 28/10/1959 | Adventure Mystery Thriller | 136.00 | USA | G | Alfred Hitchcock | $73,446.00 | 291.63 | 7.50 | ||
| 95 | Jagten | 22/11/2012 | Drama | 115.00 | Denmark | R | Thomas Vinterberg | $15,843,274.00 | 269.62 | 7.50 | ||
| 96 | Lawrence of Arabia | 29/10/1963 | Adventure Biography Drama | 228.00 | UK | PG | David Lean | $45,710,874.00 | 261.50 | 7.50 | ||
| 97 | The Sting | 21/03/1974 | Comedy Crime Drama | 129.00 | USA | PG | George Roy Hill | $156,000,000.00 | 236.29 | 7.50 | ||
| 98 | Das Boot | 18/03/1982 | Adventure Drama Thriller | 149.00 | West Germany | R | Wolfgang Petersen | $11,487,676.00 | 226.43 | 7.50 | ||
| 99 | Per qualche dollaro in più | 20/12/1965 | Western | 132.00 | Italy | R | Sergio Leone | $15,000,000.00 | 226.04 | 7.40 | ||
| 100 | Jodaeiye Nader az Simin | 21/10/2011 | Drama | 123.00 | Iran | PG-13 | Asghar Farhadi | $22,926,076.00 | 214.17 | 7.40 | ||
| 101 | Singin' in the Rain | 5/02/1953 | Comedy Musical Romance | 103.00 | USA | G | Stanley Donen | $1,864,182.00 | 213.15 | 7.40 | ||
IMDB_Data_Analysis | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J15 | J15 | =+8.7 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B:B | Cell | contains an error | text | NO |
| A1:J101 | Cell | contains an error | text | NO |
| A1:J101 | Cell | contains a blank value | text | NO |
Last edited by a moderator:
