Pull Partial data from one cell into multiple cells

**gerad** · 09-07-2011, 10:53 AM

Does anyone know of a way to pull only select data from a cell? I'm doing a project where I need to convert data from one spreadsheet which lists category id's within one cell and separated by commas (category 1, category 2, etc.) and pull each category into separate columns. Is this possible or would I need to do this all manually?

I'm using excel 07. The data is for an online marketing (SEO) project and I'm converting google category ids into supermedia ids. This is how the data converts from Google to Supermedia:

Google ID Name Supermedia ID
287 Retail Stores - Gas Stations 518994
266 Retail Stores - Convenience 517908
173 Restaurants - Coffee Shops 561138
185 Restaurants - Fast Food 509695
192 Restaurants - Ice Cream 509699
213 Restaurants - Sandwiches 522676
290 Retail Stores - Grocery & Supermarkets 517784
261 Retail Stores - Cigars, Cigarettes, Tobacco & Smoking 518899
246 Retail Stores - Beer 519754

**NBVC** · 09-07-2011, 11:02 AM

Not 100% clear....

If you want to separate at commas, use Data|Text to columns, select Delimited, click Next and select commas as delimiter, click Finish.

otherwise, post a sample workbook of before and after samples.

**ianh** · 09-20-2011, 05:47 AM

I think your referring to "text to columns" tool under the "data" tab in excel 2007/2010.

This tool will allow you to split the cell based on a criteria (comma, space,tab etc) or by a fixed width.

in your eaxample you could split it into multiple columns using "space" or two columns using the "-"