Extract cell contents separated by either a space, an & or by ,

**novodisc** · 03-15-2011, 10:41 PM

Hello there,

I have column A that contains a series of numbers separated by either a space, an & or by ,. I would like to extract each cell contents and place the extracted data in column B, C, D and so on depending on how many items were extracted.

Is there a way to do this? Please see attached file for the sample data.

Thanks in advance for helping!

**MarvinP** · 03-15-2011, 11:28 PM

Hi,

Here is an attachment with the formula that parses using a space.

If you wanted to use a period or comma also, you could search and replace all periods and commas in your text with spaces before using the attache formula.

**Leith Ross** · 03-15-2011, 11:42 PM

Hello novodisc,

The formats below will cause problems with parsing the data correctly:

DOGB0600076013 - Model DS-32B2-00
IM20007276002760 -NOPRD3-760

Since this is a small data sample, are there any more oddities like this in the actual data?

**novodisc** · 03-16-2011, 12:04 AM

Originally Posted by Leith Ross

Hello novodisc,

The formats below will cause problems with parsing the data correctly:

DOGB0600076013 - Model DS-32B2-00
IM20007276002760 -NOPRD3-760

Since this is a small data sample, are there any more oddities like this in the actual data?

Unfortunately there might be more of these odd entries...

**novodisc** · 03-16-2011, 12:08 AM

Originally Posted by MarvinP

Hi,

Here is an attachment with the formula that parses using a space.

If you wanted to use a period or comma also, you could search and replace all periods and commas in your text with spaces before using the attache formula.

Hello..
thanks for the formula.. You have helped a long way...

I'll just have to manually filter out the odd entries if there are no other ways around it.