You are here:

Excel/Separating Data in Excel



I'm not sure if my subject is right as I know very little but the spreadsheet I'm working with (Excel) and things like this so I'm not even sure if terms like "separating" and "data" are correct for my subject but I;m giving it a shot. So I'm a real beginner here.

The challenge: I have a huge list from our organization of maybe 20-25,000 names on it . I was able to figure out how to upload the list into Mailchimp (the email program I use?) so we can do mailings. Now I need break up the names on the original spreadsheet and upload separate list from it by counties and possibly by cities. Im not sure how to do this in the Mailchimp.

I think I can do it however by working in the original spreadsheet. To do this I think I can go back to the original spreadsheet and divide it by the members counties or cities and then separately submit each list into the email program.

I've been trying t figure out how to do this for over a week now and our next mailing is scheduled to go out tomorrow.

I have no idea how to do that. That is my challenge.  

I hope I've explained well enough and I hope you can help.

I'm real beginner with this stuff so I'm hoping that I will be understand the terms you use in your instruction


ANSWER: Hi Karl,

I have no experience in how mailchimp works with uploading lists, so I'm of little use there.
However, you can at least split up the information in Excel if it is in a single column. Use Data, Text to Columns to split names and countries and places. How well that performs depends on how your data is separated.

Alternatively, if you are using Excel 2013, you can use a new feature. Just start typing next to your data with the element you wish to pull from the data. SO in an empty cell adjacent to your data, type the name of the country. Move down one cell and type the name of that country too. Excel should do something called Flash fill. If it makes a mistake, simply correct the mistake and watch Excel try to correct that same mistake in other places too. Repeat for other data items like the city.

---------- FOLLOW-UP ----------


First of all thank you for responding and attempting to help. I'm sure my lack of knowledge about spreadsheets has a lot to do with my inability to clearly explain what I need to do, so that I can get the proper answer Thanks so much for attempting though

I have had Excel on my computer for years and never knew it was there until one of my co-workers sent our mail list to me and I opened it and saved it. Unfortunately for me it is not Excel 2013, It is Excel 2007

RE: My challenge - I am not trying to separate data that is in one column (like a address number and street name might be that may need to be separated), I think I'm trying to locate and move specific data from one spreadsheet to another. Example: I have a list of 25000 people (which I do)  all from 80 different counties (also true) and I want to find put how many people on the list are from county 7, take all the people out from that county (may be 200) and put them on another spreadsheet that would just have people on it from county 7 on without listing the people form the other 70 counties (thats what I mean by separating them)

If its still sounds confusing, thats the best I can do and I would understand if you chose not to respond because I clearly am lost when it comes to this stuff. If I was better versed it'd be a lot easier. Thank you so much for at least attempting to answer. I have been trying to do this for over a week so I will just keep asking around and searching . Although my blasts are scheduled to go out today


OK, here is what to do. Bear with me, there are some steps involved.

Select the tab with the data.
Make sure it is setup so that you have:

- 1 row containing the names of each column (Name, country, email, ...)
- for each person, 1 row of data

Now select the Insert tab of the ribbon and click "PivotTable".
Make sure the entire range containing your data is selected and click OK

You should get an empty pivot table.

Now from the field list that should appear on the right-hand side of your screen drag each field (in turn, EXCEPT the Country field) to the row area of that small taskpane (the rectangular box on the bottom-left of that small pane).

Drag the Country field to the Report Filter box.

The ribbon should now have a new tab called Pivottable tools. CLick on the OPtions tab of that piece.

On the left of the ribbon, you should now see a section called "PivotTable" in which you can change the name of the pivot table. No need to do that, instead, click the small dropdown arrow next to "Options" and choose "Show Report Filter Pages". Choose "Country" and click OK.

You now have each country on its own tab.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 All rights reserved.