Splitting text into columns where text is located across multiple adjacent cells?

parvezs27

New Member
Joined
Jun 27, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi everyone!

I've looked through the forums for an answer, but had no luck regarding this particular scenario, hence I'm now posting a question.

I have a messy IMDB movie dataset which I want to clean up for a personal project, I've added a mini sheet of the data at the end of the question.

As you can see, some of the data tends to be in one cell and the remainder of the data in the cell next to it and/or the cell next to that one too. The delimiter tends to be the semicolon.
For example in Cell A3 there is the movie id, movie title, release year and genre. In the cell next to it in Cell B3 is the remaining data such as duration, rating, director, score etc.

I would like to split the text into the appropriate columns, so movie id is in movie id column, genre is in genre column etc.
I used the "Convert Text to Columns" Wizard for Column A and the text did split out into different columns. However, if I continue using Convert Text to Columns, I would have to select the cell with the data, go through the Convert Text to Columns wizard again and choose the destination cell for the data to go into, one by one, which would take a long time.

Is there an easier way to get all this data into the right columns?

Assistance would be appreciated, cheers!

messy_IMDB_dataset.csv
ABCDE
1IMBD title ID;Original titlÊ;Release year;Genrë¨;Duration;Country;Content Rating;Director;;Income; Votes ;Score
2tt0111161;The Shawshank Redemption;1995-02-10;Drama;142;USA;R;Frank Darabont;;$ 28815245;2.278.845;9.3
3tt0068646;The Godfather;09 21 1972;Crime Drama;175;USA;R;Francis Ford Coppola;;$ 246120974;1.572.674;9.2
4tt0468569;The Dark Knight; 23 -07-2008;Action Crime Drama;152;US;PG-13;Christopher Nolan;;$ 1005455211;2.241.615;9.
5tt0071562;The Godfather: Part II;1975-09-25;Crime Drama;220;USA;R;Francis Ford Coppola;;$ 4o835783;1.098.714;90
6tt0110912;Pulp Fiction;1994-10-28;Crime Drama; ;USA;R;Quentin Tarantino;;$ 222831817;1.780.147;89f
7tt0167260;The Lord of the Rings: The Return of the King;22 Feb 04;Action Adventure Drama;201;New Zealand;PG-13;Peter Jackson;;$ 1142271098;1.604.280;08.9
8tt0108052;Schindler's List;1994-03-11;Biography Drama History;Nan;USA;R;Steven Spielberg;;$ 322287794;1.183.248;8.9
9tt0050083;12 Angry Men;1957-09-04;Crime Drama;96;USA;Not Rated;Sidney Lumet;;$ 576;668.473;8.9
10tt1375666;Inception;2010-09-24;Action Adventure Sci-Fi;148;USA;PG-13;Christopher Nolan;;$ 869784991;2.002.816;8..8
11tt0137523;Fight Club;10-29-99;Drama;Inf;UK;R;David Fincher;;$ 101218804;1.807.440;8.8
12tt0109830;Forrest Gump;1994-10-06;Drama Romance;142;USA;PG-13;Robert Zemeckis;;$ 678229452;1.755.490;8:8
13tt0120737;The Lord of the Rings: The Fellowship of the Ring;2002-01-18;Action Adventure Drama;178c;New Zesland;PG-13;Peter Jackson;;$ 887934303;1.619.920;8.8
14tt0060196;Il buono il brutto il cattivo;23rd December of 1966 ;Western;161;Italy;Approved;Sergio Leone;;$ 25252481;672.499;8.8
15;;;;;;;;;;;
16tt0133093;The Matrix;1999-05-07;Action Sci-Fi;NULL;USA;R;Lana Wachowski Lilly Wachowski;;$ 465718588;1.632.315;++8.7
17tt0167261;The Lord of the Rings: The Two Towers;01/16-03;Action Adventure Drama;179;New Zeland;PG-13;Peter Jackson;;$ 951227416;1.449.778;8.7.
18tt0080684;Star Wars: Episode V - The Empire Strikes Back;1980-09-19;Action Adventure Fantasy;Not Applicable;USA;PG;Irvin Kershner;;$ 549265501;1.132.073;87.00E+00
19tt0099685;Goodfellas;1990-09-20;Biography Crime Drama;146;USA;R;Martin Scorsese;;$ 46879633;991.505;8.7
20tt0073486;One Flew Over the Cuckoo's Nest;18/11/1976;Drama;-;USA;R;Milos Forman;;$ 108997629;891.071;8.7
21tt0816692;Interstellar;2014-11-06;Adventure Drama Sci-Fi;169;USA;PG-13;Christopher Nolan;;$ 696742056;1.449.256;8.6
22tt0114369;Se7en;1995-12-15;Crime Drama Mystery;127;USA;R;David Fincher;;$ 327333559;1.402.015;8.6
23tt0102926;The Silence of the Lambs;1991-03-05;Crime Drama Thriller;118;USA;R;Jonathan Demme;;$ 272753884;1.234.134;86
24tt0076759;Star Wars;1977-10-20;Action Adventure Fantasy;121;USA;PG;George Lucas;;$ 775768912;1.204.107;8.6
25tt0120815;Saving Private Ryan;1998-10-30;Drama War;169;USA;R;Steven Spielberg;;$ 482349603;1.203.825;8.6
26tt0120689;The Green Mile;2000-10-03;Crime Drama Fantasy;189;US.;R;Frank Darabont;;$ 286801374;1.112.336;8.6
27tt0317248;Cidade de Deus;2003-05-09;Crime Drama;130;Brazil;R;Fernando Meirelles Kátia Lund;;$ 30680793;685.856;8.6
28tt0245429;Sen to Chihiro no kamikakushi;2003-04-18;Animation Adventure Family;125;Japan;PG;Hayao Miyazaki;;$ 355467056;626.693;8.6
29tt0118799;La vita B9 bella;1997-12-20;Comedy Drama Romance;116;Italy1;#N/A;Roberto Benigni;;$ 230098753;605.648;8.6
30tt6751668;Gisaengchung;2019-11-07;Comedy Drama Thriller;132;South Korea;#N/A;Bong Joon Ho;;$ 257604912;470.931;8.6
31tt0038650;It's a Wonderful Life;1948-03-11;Drama Family Fantasy;130;USA;PG;Frank Capra;;$ 6130720;388.310;8.6
32tt0047478;Shichinin no samurai;1955-08-19;Action Adventure Drama;207;Japan;Unrated;Akira Kurosawa;;$ 322773;307.958;8.6
33tt0172495;Gladiator;2000-05-19;Action Adventure Drama;155;USA;R;Ridley Scott;;$ 465361176;1.308.193;8.5
34tt0407887;The Departed;2006-10-27;Crime Drama Thriller;151;USA;R;Martin Scorsese;;$ 291465034;1.159.703;8.5
35tt0482571;The Prestige;2006-12-22;Drama Mystery Sci-Fi;130;UK;PG-13;Christopher Nolan;;$ 109676311;1.155.723;8.5
36tt0088763;Back to the Future;1985-10-18;Adventure Comedy Sci-Fi;116;USA;PG;Robert Zemeckis;;$ 388774684;1.027.330;8.5
37tt0120586;American History X;1999-08-27;Drama;119;USA;R;Tony Kaye;;$ 23875127;1.014.218;8.5
38tt0110413;Léon;1995-04-07;Action Crime Drama;110;France;#N/A;Luc Besson;;$ 19552639;1.007.598;8.5
39tt0103064;Terminator 2: Judgment Day;1991-12-19;Action Sci-Fi;137;USA;R;James Cameron;;$ 520884847;974.970;8.4
40tt0114814;The Usual Suspects;1995-11-30;Crime Mystery Thriller;106;USA;R;Bryan Singer;;$ 23341568;968.947;8.4
41tt0110357;The Lion King;1994-11-25;Animation Adventure Drama;88;USA;G;Roger Allers Rob Minkoff;;$ 968511805;917.248;8.4
42tt7286456;Joker;2019-10-03;Crime Drama Thriller;122;USA;#N/A;Todd Phillips;;$ 1074251311;855.097;8.4
43tt1675434;Intouchables;2012-02-24;Biography Comedy Drama;112;France;#N/A;Olivier Nakache Éric Toledano;;$ 426588510;736.691;8.4
44tt0253474;The Pianist;2002-10-25;Biography Drama Music;150;UK;R;Roman Polanski;;$ 120072577;707.942;8.4
45tt2582802;Whiplash;2015-02-12;Drama Music;106;USA;R;Damien Chazelle;;$ 48983260;690.732;8.4
46tt0054215;Psycho;1960-10-28;Horror Mystery Thriller;109;USA;R;Alfred Hitchcock;;$ 32008644;586.765;8.3
47tt0034583;Casablanca;21-11-46;Drama Romance War;102;USA;PG;Michael Curtiz;;$ 4374761;509.953;8.3
48tt0064116;C'era una volta il West;1968-12-21;Western;165;Italy;PG-13;Sergio Leone;;$ 112911;295.220;8.3
49tt0095327;Hotaru no haka;2015-10-11;Animation Drama War;89;Japan;#N/A;Isao Takahata;;$ 516962;225.438;8.3
50tt0095765;Nuovo Cinema Paradiso;1988-11-17;Drama;155;Italy;#N/A;Giuseppe Tornatore;;$ 13826605;223.050;8.3
51tt0027977;Modern Times;1937-03-12;Comedy Drama Family;87;USA;G;Charles Chaplin;;$ 457688;211.250;8.3
52tt1345836;The Dark Knight Rises;2012-08-29;Action Adventure;164;UK;PG-13;Christopher Nolan;;$ 1081133191;1.480.582;8.3
53tt1853728;Django Unchained;2013-01-17;Drama Western;165;USA;R;Quentin Tarantino;;$ 425368238;1.317.856;8.3
54tt0209144;Memento;2001-01-19;Mystery Thriller;113;USA;R;Christopher Nolan;;$ 39970386;1.098.879;8.2
55tt0910970;WALL·E;2008-10-17;Animation Adventure Family;98;USA;G;Andrew Stanton;;$ 521311860;974.734;8.2
56tt0081505;The Shining;1980-12-22;Drama Horror;146;UK;R;Stanley Kubrick;;$ 46520613;869.480;8.2
57tt0082971;Raiders of the Lost Ark;1981-06-12;Action Adventure;115;USA;PG;Steven Spielberg;;$ 390133212;865.510;8.2
58tt4154756;Avengers: Infinity War;2018-04-25;Action Adventure Sci-Fi;149;USA;#N/A;Anthony Russo Joe Russo;;$ 2048359754;796.486;8.2
59tt0078748;Alien;1979-10-25;Horror Sci-Fi;117;UK;R;Ridley Scott;;$ 108110316;768.874;8.2
60tt4154796;Avengers: Endgame;2019-04-24;Action Adventure Drama;181;USA;#N/A;Anthony Russo Joe Russo;;$ 2797800564;754.786;8.2
61tt0078788;Apocalypse Now;1979-12-18;Drama Mystery War;147;USA;R;Francis Ford Coppola;;$ 91968688;591.251;8.2
62tt0364569;Oldeuboi;2005-05-06;Action Drama Mystery;120;South Korea;R;Chan-wook Park;;$ 15002116;501.082;8.1
63tt0057012;Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb;1964-04-03;Comedy;95;UK;PG;Stanley Kubrick;;$ 9443876;441.115;8.1
64tt0047396;Rear Window;1955-04-14;Mystery Thriller;112;USA;#N/A;Alfred Hitchcock;;$ 37032034;432.390;8.1
65tt2380307;Coco;2017-12-28;Animation Adventure Family;105;USA;#N/A;Lee Unkrich Adrian Molina;;$ 807083670;352.455;8.1
66tt0405094;Das Leben der Anderen;2007-04-06;Drama Mystery Thriller;137;Germany;R;Florian Henckel von Donnersmarck;;$ 77356942;349.642;8.1
67tt4633694;Spider-Man: Into the Spider-Verse;2018-12-25;Animation Action Adventure;117;USA;#N/A;Bob Persichetti Peter Ramsey;;$ 375540831;335.892;8.1
68tt1187043;3 Idiots;2009-12-25;Comedy Drama;170;India;#N/A;Rajkumar Hirani;;$ 60262836;332.217;8.1
69tt0119698;Mononoke-hime;2000-05-19;Animation Adventure Fantasy;134;Japan;PG-13;Hayao Miyazaki;;$ 169785629;331.045;8.0
70tt0087843;Once Upon a Time in America;1984-09-28;Crime Drama;229;USA;R;Sergio Leone;;$ 5472914;302.317;8.0
71tt0032553;The Great Dictator;1945-06-25;Comedy Drama War;125;USA;#N/A;Charles Chaplin;;$ 969879;197.381;8.0
72tt0043014;Sunset Blvd.;The 6th of marzo year 1951;Drama Film-Noir;110;USA;#N/A;Billy Wilder;;$ 299645;195.789;8.0
73tt0361748;Inglourious Basterds;2009-10-02;Adventure Drama War;153;Germany;R;Quentin Tarantino;;$ 321455689;1.229.958;8.0
74tt0169547;American Beauty;2000-01-21;Drama;122;USA;R;Sam Mendes;;$ 356296601;1.049.009;8.0
75tt0112573;Braveheart;1995-12-01;Biography Drama History;178;USA;R;Mel Gibson;;$ 213216216;941.683;7.9
76tt0086190;Star Wars: Episode VI - Return of the Jedi;1983-10-21;Action Adventure Fantasy;131;USA;PG;Richard Marquand;;$ 475347111;928.036;7.9
77tt0105236;Reservoir Dogs;1992-10-09;Crime Drama Thriller;99;USA;R;Quentin Tarantino;;$ 2889963;896.551;7.9
78tt0338013;Eternal Sunshine of the Spotless Mind;2004-10-22;Drama Romance Sci-Fi;108;USA;R;Michel Gondry;;$ 74036715;889.875;7.9
79tt0114709;Toy Story;1996-03-22;Animation Adventure Comedy;81;USA;G;John Lasseter;;$ 404265438;864.461;7.9
80tt0119217;Good Will Hunting;1998-03-06;Drama Romance;126;USA;R;Gus Van Sant;;$ 225933435;837.379;7.8
81tt0208092;Snatch;2001-03-16;Comedy Crime;104;UK;R;Guy Ritchie;;$ 83557872;766.589;7.8
82tt0180093;Requiem for a Dream;2000-12-15;Drama;102;USA;R;Darren Aronofsky;;$ 7390108;748.291;7.8
83tt0066921;A Clockwork Orange;1972-09-07;Crime Drama Sci-Fi;136;UK;#N/A;Stanley Kubrick;;$ 26903440;740.301;7.8
84tt0435761;Toy Story 3;2010-07-07;Animation Adventure Comedy;103;USA;G;Lee Unkrich;;$ 1066969703;739.717;7.8
85tt0086250;Scarface;1984-02-34;Crime Drama;170;USA;R;Brian De Palma;;$ 66023585;721.343;7.8
86tt0075314;Taxi Driver;1976-13-24;Crime Drama;114;USA;R;Martin Scorsese;;$ 28441292;703.264;7.7
87tt0211915;Le fabuleux destin d'Amélie Poulain;2002-01-25;Comedy Romance;122;France;R;Jean-Pierre Jeunet;;$ 173924742;690.480;7.7
88tt0093058;Full Metal Jacket;1987-10-09;Drama War;116;UK;#N/A;Stanley Kubrick;;$ 46357676;658.175;7.7
89tt0090605;Aliens;1986-09-24;Action Adventure Sci-Fi;137;USA;R;James Cameron;;$ 131384634;639.500;7.7
90tt0062622;2001: A Space Odyssey;1968-12-12;Adventure Sci-Fi;149;UK;G;Stanley Kubrick;;$ 68989547;587.866;7.6
91tt0033467;Citizen Kane;1948-11-25;Drama Mystery;119;USA;#N/A;Orson Welles;;$ 1594107;389.322;7.6
92tt8579674;1917;2020-01-23;Drama War;119;USA;#N/A;Sam Mendes;;$ 384857224;362.897;7.6
93tt0086879;Amadeus;1985-02-15;Biography Drama History;160;USA;R;Milos Forman;;$ 52066791;361.028;7.6
94tt0052357;Vertigo;1958-11-18;Mystery Romance Thriller;128;USA;#N/A;Alfred Hitchcock;;$ 7796389;352.786;7.5
95tt0053125;North by Northwest;1959-10-28;Adventure Mystery Thriller;136;USA;#N/A;Alfred Hitchcock;;$ 73446;291.628;7.5
96tt2106476;Jagten;2012-11-22;Drama;115;Denmark;R;Thomas Vinterberg;;$ 15843274;269.616;7.5
97tt0056172;Lawrence of Arabia;1963-10-29;Adventure Biography Drama;228;UK;PG;David Lean;;$ 45710874;261.504;7.5
98tt0070735;The Sting;1974-03-21;Comedy Crime Drama;129;USA;PG;George Roy Hill;;$ 156000000;236.285;7.5
99tt0082096;Das Boot;1982-03-18;Adventure Drama Thriller;149;West Germany;R;Wolfgang Petersen;;$ 11487676;226.427;7.5
100tt0059578;Per qualche dollaro in più;1965-12-20;Western;132;Italy;#N/A;Sergio Leone;;$ 15000000;226.039;7.4
101tt1832382;Jodaeiye Nader az Simin;2011-10-21;Drama;123;Iran;PG-13;Asghar Farhadi;;$ 22926076;214.165;7.4
102tt0045152;Singin' in the Rain;1953-02-05;Comedy Musical Romance;103;USA;#N/A;Stanley Donen;;$ 1864182;213.152;7.4
messy_IMDB_dataset
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Welcome to the MrExcel board!

Are you looking for a manual/formula approach, macro or perhaps power query?

If you begin typing =TEXT into a cell, do you get these highlighted options?

1656387841415.png
 
Upvote 0
Welcome to the MrExcel board!

Are you looking for a manual/formula approach, macro or perhaps power query?

If you begin typing =TEXT into a cell, do you get these highlighted options?

View attachment 68084
Hi @Peter_SSs!

Thanks for the response 😁

Still very much a beginner in excel and only familiar with certain functions and tools. Macro's and Power Query are totally new concepts for me currently, but willing to learn as I want to continue developing my skillset. 💪

No I cannot see the highlighted options, screenshot below:
1656391267670.png
 
Upvote 0
Macro's and Power Query are totally new concepts for me currently,
OK, for a start, let's try a formula/manual approach and see how it goes.

Your "messy" data appears to spread across 5 columns A:E so ..
  1. In cell F1 put this formula =CONCAT(A1:E1)
  2. Now drag that down the column to the bottom of the data.
  3. Select all of column F formulas and Copy -> PasteSpecial... (Values)
  4. With all the column F data (now values, not formulas) still selected, do Text to Columns (Delimited) with semicolon as the only delimiter.
 
Upvote 0
Solution
I
OK, for a start, let's try a formula/manual approach and see how it goes.

Your "messy" data appears to spread across 5 columns A:E so ..
  1. In cell F1 put this formula =CONCAT(A1:E1)
  2. Now drag that down the column to the bottom of the data.
  3. Select all of column F formulas and Copy -> PasteSpecial... (Values)
  4. With all the column F data (now values, not formulas) still selected, do Text to Columns (Delimited) with semicolon as the only delimiter.
It worked! Thank you! 🙏
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
No worries at all, appreciate the assistance!

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.
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,223,882
Messages
6,175,164
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top