Extracting data from statements is a very common use case in Excel. And when data is structured in a certain way, separated by commas or semicolons, etc., it is pretty straightforward to achieve the desired output using Excel functions. When data is not separated by a comma or any special character and is only separated by spaces, it becomes a little challenging to separate it correctly.
Now before we jump into working with data that is only separated by spaces, let’s see an example of how you can separate data from a single statement, or address in this case when there are commas present to separate the different components.
We will highlight the data cell that contains the address. Then click on the ‘Data’ tab and go to ‘Text to Columns’. A new window will appear, as shown above. You will have to choose between ‘Delimited’ and ‘Fixed Width’ options to separate the data.
A delimiter can be a space, comma, semicolon, or any special character which separates the data components in a statement.
Here, since we have our data separated by commas, we are going to use the delimited option. For the delimiter, we will choose ‘Comma’ and click OK.
Before you click OK, you will also be able to see data preview of how the separated data will appear.
Next, we can define the cell reference where we want the separated data to start appearing. Let’s say in this instance, cell $A$2. Then click Finish. Your data will be separated into different parts at the places where commas were present in the initial data.
Now to work on data that is only separated by spaces, watch the video below and also follow along by doing the same thing with the sample data provided below. Just copy and paste this data on your excel sheet and then practice along.
Watch Below Video and Work Alongside This With Sample Data Below
Copy and Paste This Sample Data in Excel for Practice
Below is the sample data with fake names and addresses that you can copy directly to Excel and practice along, following the steps in above video. By the end of this task, you will feel proficient with working with such data, separating and cleaning up. As a bonus, you will get to learn and practice various formulas and functions which can add to your Excel skills.
| NAME | ADDRESS |
| Remington Hoover | 100 Douglas Abbeville AL 36310-5827 USA |
| Dilan Combs | 100 Elm Abbeville AL 36310-2432 USA |
| Remington Villegas | 100 Lime Springs Abbeville AL 36310-5711 USA |
| Lee Benjamin | 100 Moseley Abbeville AL 36310-2628 USA |
| Alaina Conrad | 100 W Kelly Abbeville AL 36310-2112 USA |
| Maren Willis | 101 Arrowhead Abbeville AL 36310-5702 USA |
| Irene Willis | 1901 County Road 40 Abbeville AL 36310-3011 USA |
| Donovan Harmon | 2000 Powell Abbeville AL 36310-5674 USA |
| Rosalia Lester | 201 E Park Abbeville AL 36310-2533 USA |
| Kinley Wolfe | 2801 Calhoun Abbeville AL 36310-5649 USA |
| Wilder Rosales | 401 County Road 133 Abbeville AL 36310-5102 USA |
| Bear Clay | 401 Otho Abbeville AL 36310-5781 USA |
| Aliana Aguilar | 4600 County Road 97 Abbeville AL 36310-5810 USA |
| Jessie Douglas | 500 Mill Creek Abbeville AL 36310-5716 USA |
| Jeanna Rosales | 519 Kirkland Abbeville AL 36310-2735 USA |
| Milo Macdonald | 600 Singletary Abbeville AL 36310-2070 USA |
| Derek Barrett | 8601 County Road 97 Abbeville AL 36310-5731 USA |
| Kendall Rivers | 8901 County Road 97 Abbeville AL 36310-5735 USA |
| Leah Wang | 10701 E Pine Valley Hackberry AZ 86411-7014 USA |
| Jayden Kelly | 10701 N Pine Valley Hackberry AZ 86411-7039 USA |
| Rhea McKay | 10800 E Spring Valley Hackberry AZ 86411-2049 USA |
| Virginia Benson | 11301 E Spring Valley Hackberry AZ 86411-2009 USA |
| Desmond Wagner | 14300 E Dicks Camp Hackberry AZ 86411-2038 USA |
| Kamden Hall | 14500 E Dicks Camp Hackberry AZ 86411-2046 USA |
| Ruby Rowe | 6201 N Mountain Spring Hackberry AZ 86411-7021 USA |
| Maeve Torres | 7201 N Friedrichs Ranch Hackberry AZ 86411-7003 USA |
| Cohen Bradford | 9601 E El Norte Hackberry AZ 86411-1200 USA |
| Madilynn Stein | 28731 Eagleton Agoura Hills CA 91301-1758 USA |
| Joey Mora | 29229 Camwood Agoura Hills CA 91301-1561 USA |
| Alexis Montgomery | 29281 Agoura Agoura Hills CA 91301-2597 USA |
| Briar Curtis | 29395 Agoura Agoura Hills CA 91301-2504 USA |
| Alayna McCullough | 5366 Lake Linder Agoura Hills CA 91301-1958 USA |
| Wesley Ibarra | 5708 Skyview Agoura Hills CA 91301-1693 USA |
| Jemma Ross | 5900 Cheseboro Agoura Hills CA 91301-1830 USA |
| Creed Larson | 6144 3/4 Cheseboro Agoura Hills CA 91301-1809 USA |
| Denise Wong | 1100 Walnut Grand Junction CO 81501-2956 USA |
| Walter Wall | 1101 North Grand Junction CO 81501-3142 USA |
| Scout Leon | 1501 N 1st Grand Junction CO 81501-2141 USA |
| Gatlin Larsen | 16000 Highway 90 Bedrock CO 81411-9700 USA |
| Indie Michael | 1630 30th Boulder CO 80301-1044 USA |
| Maximiliano Nava | 1900 N 3rd Grand Junction CO 81501-2114 USA |
| Augustine Houston | 3100 Bell Boulder CO 80301-2277 USA |
| Bronson Nguyen | 3250 O’Neal Boulder CO 80301-1460 USA |
| Benson Alfaro | 3295 34th Boulder CO 80301-1977 USA |
| Yasmin Kaur | 3375 34th Boulder CO 80301-1995 USA |
| Daniel George | 3752 Monterey Boulder CO 80301-1523 USA |
| Nova Wilson | 3800 Paseo Del Prado Boulder CO 80301-1522 USA |
| Kassidy Robertson | 401 N Sherwood Grand Junction CO 81501-2174 USA |
| Adelyn Cain | 4100 Stone Boulder CO 80301-1609 USA |
| Marshall Farrell | 501 Orchard Grand Junction CO 81501-2161 USA |
| Emiliano McConnell | 601 N 6th Ave Grand Junction CO 81501-2709 USA |
| Xiomara Waller | 6750 Highway 90 Way Bedrock CO 81411-9709 USA |
| Jayda Ray | 700 N 11th Ave Grand Junction CO 81501-3210 USA |
| Arlo Preston | 950 North Grand Junction CO 81501-3260 USA |
| Paislee Chambers | 100 Bayside Dewey Beach DE 19971-3227 USA |
| Zara Russo | 100 Canal Delaware City DE 19706-8713 USA |
| Marley Jensen | 100 Monroe Delaware City DE 19706-7740 USA |
| Andre Carroll | 112 Clinton Delaware City DE 19706-7800 USA |
| Reyna Herring | 119 Collins Dewey Beach DE 19971-2427 USA |
| Zainab Quintero | 1301 Coastal Dewey Beach DE 19971-2408 USA |
| Thatcher Morrow | 1400 Coastal Dewey Beach DE 19971-2410 USA |
| Lexie Park | 1701 Coastal Dewey Beach DE 19971-2363 USA |
| Keaton Maddox | 1905 Coastal Dewey Beach DE 19971-2344 USA |
| Ali Henson | 201 Adams Delaware City DE 19706-7763 USA |
| Henrik Best | 21 Rodney Dewey Beach DE 19971-2343 USA |
| Jane Hopkins | 301 Jefferson Delaware City DE 19706-7753 USA |
| Raya Savage | 500 Cook Delaware City DE 19706-8755 USA |
| Jamie Frye | 704 5th Delaware City DE 19706-7811 USA |
| Daxton Mann | 800 The Dewey Beach DE 19971-2438 USA |
| Lylah Pruitt | 801 Clinton Delaware City DE 19706-7717 USA |
| Kinsley Fuller | 900 4th Delaware City DE 19706-7794 USA |
| Wallace Hart | 100 Mutiny Merritt Island FL 32952-2556 USA |
| Nadia Hull | 1900 Bahama Miramar FL 33023-2639 USA |
| Leilani Portillo | 205 Buccaneer Merritt Island FL 32952-2517 USA |
| Karter Farmer | 2100 Flamingo Miramar FL 33023-3523 USA |
| Jamison Stein | 301 Fisher Island Miami Beach FL 33109-0311 USA |
| Salem Moses | 7100 Venetian Miramar FL 33023-2646 USA |
| Orion Mack | 7500 Venetian Miramar FL 33023-2516 USA |
| Gemma Bishop | 76 E Merritt Merritt Island FL 32952-3405 USA |
| Maleah Patterson | 100 Hickory Flat Alpharetta GA 30004-2309 USA |
| Graysen Garza | 100 Hickory Hill Alpharetta GA 30004-7007 USA |
| River Davidson | 1200 Bluegrass Lakes Alpharetta GA 30004-0548 USA |
| Dante Allison | 155 Mauldin Alpharetta GA 30004-2912 USA |
| Madelyn Chavez | 320 Swingline Alpharetta GA 30004-3135 USA |
| Fisher Rush | 41 Marietta Atlanta GA 30301-8630 USA |
| Christian Rogers | 4501 Hamptons Club Alpharetta GA 30004-2969 USA |
| Chelsea Morris | 4900 Atlanta Alpharetta GA 30004-2921 USA |
| Amir Sims | 600 Oakstone Alpharetta GA 30004-2574 USA |
| Lena Farley | 601 Gantt Alpharetta GA 30004-5201 USA |
| Kenzo Meyers | 1000 221st Edgewood IA 52042-8718 USA |
| Lennox Tucker | 105 W Southside Earlville IA 52041-2564 USA |
| Calum Paul | 109 S West Earlville IA 52041-9643 USA |
| Leyla Clements | 115 N Washington Edgewood IA 52042-4719 USA |
| Elise Farmer | 125 1/2 W 1st Earlham IA 50072-7710 USA |
| Daphne Berg | 13 E 3rd Earlville IA 52041-2579 USA |
| Jack Adkins | 1500 280th Earlville IA 52041-8621 USA |
| Jamison Lu | 1700 Elmwood Earlham IA 50072-8524 USA |
| Emelia Finley | 1900 260th Earlville IA 52041-8660 USA |
| Xzavier Austin | 19700 365th Earlham IA 50072-8122 USA |
| Kylan Bates | 19800 390th Edgewood IA 52042-8072 USA |
| Magnus Velazquez | 200 5th Earlville IA 52041-9627 USA |
| Emani Little | 2000 130th Earlham IA 50072-8629 USA |
| Mariah Gentry | 201 E 4th Earlville IA 52041-9623 USA |
| Cayson Holland | 204 Elizabeth Earlville IA 52041-2533 USA |
| Sophia Odom | 21200 390th Edgewood IA 52042-8067 USA |
| Alivia Lin | 255 Se 3rd Earlham IA 50072-1140 USA |
| Esther Lee | 2900 230th Earlville IA 52041-8518 USA |
| Reese Manning | 33205 E 132nd Carnesville IA 64632-8713 USA |
| Emmie Choi | 33499 Houston Earlham IA 50072-5552 USA |
| Ember Ballard | 33600 Littleport Edgewood IA 52042-8171 USA |
| Khari Duncan | 34877 Filmore Earlham IA 50072-5586 USA |
| Henry Jones | 37400 Hilton Edgewood IA 52042-8038 USA |
| Jaliyah Rodriguez | 39200 Hilton Edgewood IA 52042-8005 USA |
| Jennifer Alfaro | 400 NW Lynn Earlham IA 50072-7758 USA |
| Boa Daniels | 600 W Union Edgewood IA 52042-8163 USA |
| Madilyn Marsh | 601 W Union Edgewood IA 52042-8157 USA |
| Conor Rocha | 800 NW Spruce Earlham IA 50072-1134 USA |
| Alicia Berry | 1200 N Westside Dayton ID 83232-5143 USA |
| Dylan Escobar | 1400 N 4000 Dayton ID 83232-5155 USA |
| Journee Frost | 1900 N 4000 Dayton ID 83232-5130 USA |
| Adonis Person | 2500 N Westside Dayton ID 83232-5148 USA |
| Zachariah Hamilton | 2600 N Westside Dayton ID 83232-5105 USA |
| Yaretzi Chang | 3201 W 2000 Dayton ID 83232-5108 USA |
| Dylan Humphrey | 4000 W 3200 Dayton ID 83232-5101 USA |
| Mackenzie Nelson | 700 N Westside Dayton ID 83232-5201 USA |
| Paxton Floyd | 7791 N Aqua Dalton Gardens ID 83815-4991 USA |
| Ari Griffith | 800 N Westside Dayton ID 83232-5139 USA |
| Logan Butler | 100 S Mulberry Calhoun IL 62419-1000 USA |
| Bridget Gaines | 1001 Railroad Camp Grove IL 61424-2100 USA |
| Ian Avery | 101 S 1st Cambridge IL 61238-1215 USA |
| Peyton Goodman | 101 Smith Caldwell IL 61911-6004 USA |
| Carolina Jones | 101 Yantis Caldwell IL 61911-6009 USA |
| Margot Wilkins | 1060 2nd Camp Grove IL 61424-9800 USA |
| Meghan Caldwell | 117 S East Cambridge IL 61238-1216 USA |
| Dillon Guerrero | 11700 Osco Cambridge IL 61238-9517 USA |
| Anais Thomas | 1500 N Oak Tree Calhoun IL 62419-2236 USA |
| Lily Fitzgerald | 1501 Cr 1900n Caldwell IL 61911-6000 USA |
| Jessie Roberson | 1642 Harbor Calumet City IL 60409-1630 USA |
| Yusuf Stafford | 1900 Siomara Camp Grove IL 61424-9601 USA |
| William Benton | 1901 N Saint Marie Calhoun IL 62419-2012 USA |
| Cali Blair | 2 Paxton Calumet City IL 60409-1510 USA |
| Athena McMahon | 200 Jennifer Calumet City IL 60409-1417 USA |
| Dario Wright | 200 Smith Caldwell IL 61911-6005 USA |
| Marco Frederick | 300 S West Cambridge IL 61238-1430 USA |
| Madelynn Klein | 307 S Ridge Cambridge IL 61238-1403 USA |
| Jakob Villegas | 397 Campbell Calumet City IL 60409-2016 USA |
| Shepherd Banks | 427 Paxton Calumet City IL 60409-2212 USA |
| Rylan Farmer | 601 W Locust Cambridge IL 61238-1230 USA |
| Sariah Mason | 9300 N 1200th Cambridge IL 61238-9214 USA |
| Brandon Roberson | 9800 N 1300th Cambridge IL 61238-9439 USA |
| Sasha Zavala | General Delivery Cambridge IL 61238-9999 USA |
| Emmie McClure | 1 Main Bellmore IN 47830-2000 USA |
| Alistair Rocha | 100 East Battle Ground IN 47920-9740 USA |
| Sam Peters | 1000 Palomino Bargersville IN 46106-8412 USA |
| Raul Bullock | 1600 Kim’s Battle Ground IN 47920-9462 USA |
| Paloma Browning | 1900 Kim’s Battle Ground IN 47920-9468 USA |
| Leila Combs | 200 Depot Bargersville IN 46106-8447 USA |
| Milo Mayer | 2000 N 575 Bargersville IN 46106-9127 USA |
| Preston Sims | 2900 Antioch Battle Ground IN 47920-9416 USA |
| Lena Bautista | 2900 Jaydin Battle Ground IN 47920-9459 USA |
| Ahmad Rangel | 3270 Amber Bargersville IN 46106-8549 USA |
| Ainoi Church | 3400 N Road 450 Bargersville IN 46106-9224 USA |
| Terrance Escobar | 4300 N Road 500 Bargersville IN 46106-9252 USA |
| Rohan Pierce | 4600 E Scenic Battle Ground IN 47920-8054 USA |
| Aliya Wyatt | 500 Industrial Bargersville IN 46106-8399 USA |
| Erin Matthews | 500 N Road 400 Bargersville IN 46106-9346 USA |
| Vicente Avalos | 511 Main Battle Ground IN 47920-9723 USA |
| Ansley Fuentes | 5700 E County Road 900 Bainbridge IN 46105-9437 USA |
| Jacob Faulkner | 6400 N County Road 500 Bainbridge IN 46105-9581 USA |
| Troy Palmer | 7300 N County Road 725 Bainbridge IN 46105-9426 USA |
| Arabella Andersen | 7400 State Road 43 Battle Ground IN 47920-9415 USA |
| Gloria Aguilar | 7500 Banta Woods Bargersville IN 46106-8734 USA |
| Bowen Boyle | 8200 N County Road 675 Bainbridge IN 46105-9470 USA |
| Juniper Anderson | 8600 E County Road 800 Bainbridge IN 46105-9527 USA |
| Winnie Cordova | 9300 Tyler Battle Ground IN 47920-9466 USA |
| Leonardo Floyd | 100 NW 130 Albert KS 67511-9232 USA |
| Yaretzi Franco | 101 NW 130 Albert KS 67511-9241 USA |
| Jaxon Quinn | 101 S King Abbeville KS 67510-5121 USA |
| Dane Doyle | 1301 NW 60 Albert KS 67511-9209 USA |
| Osiris Chavez | 15400 Road E9 Almena KS 67622-9837 USA |
| Nevaeh Willis | 166 Rr 1 Almena KS 67622-9781 USA |
| Cannon McLean | 17700 W 4th Abbeville KS 67510-9502 USA |
| Natalia Mitchell | 17801 W Illinois Abbeville KS 67510-9543 USA |
| Sky Skinner | 19201 W Blanchard Abbeville KS 67510-9519 USA |
| Heaven McFarland | 2 N Sego Abbeville KS 67510-9566 USA |
| Talon Mayo | 200 N Peace Abbeville KS 67510-9430 USA |
| Jessie Matthews | 2000 2nd Albert KS 67511-9254 USA |
| Zaire Villegas | 2002 Main Albert KS 67511-9998 USA |
| Araya Noble | 201 N Peace Abbeville KS 67510-9431 USA |
| Annalise Bennett | 301 NW 100 St. Albert KS 67511-9218 USA |
| Elaina Owen | 5601 S Sterling Abbeville KS 67510-9467 USA |
| Gage Luna | 57 Rr 1 St. Albert KS 67511-9634 USA |
| Journey Lloyd | 62 Rr 1 St. Albert KS 67511-9636 USA |
| Tristan Schultz | 700 Bourton Almena KS 67622-9757 USA |
| Preston Patterson | 700 Main Albert KS 67511-9203 USA |
| Kaylee Meyer | 700 Pratt Almena KS 67622-9603 USA |
| Idris Maldonado | 7001 S Hodge Abbeville KS 67510-9535 USA |
| Ridge Wood | 7001 S Hodge Abbeville KS 67510-9535 USA |
| Briella Delarosa | 9 Rr 1 St. Almena KS 67622-9706 USA |
| Remington Goodwin | 8100 Highway 1401 Garfield KY 40140-5031 USA |
