Formula needed

[IneedHel]

I run a report that I export into excel and it will have multiple names in one column separated by a comma. How can I move the crew members into their own column?

[TABLE="width: 537"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD] Current
COLUMN A [/TD]
[TD="colspan: 3"] Needed
COLUMN A COLUMN B COLUMN C[/TD]
[/TR]
[TR]
[TD] Crew Member[/TD]
[TD]Crew Member[/TD]
[TD]Crew Member[/TD]
[TD]Crew Member[/TD]
[/TR]
[TR]
[TD]chris porter, steve porter, cindy porter[/TD]
[TD]chris porter[/TD]
[TD]steve porter[/TD]
[TD]cindy porter [/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

You can do this with a formula:

Book1
	A	B	C	D
1	Crew Member	Crew Member	Crew Member	Crew Member
2	chris porter, steve porter, cindy porter	chris porter	steve porter	cindy porter
Sheet60

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),COLUMNS($B1:B1)*100-99,100))

Formula copied across as far as needed.

If you're looking to REPLACE Column A with the separated values, you'll need VBA.

 

[jtakw]

Hi,

I need to correct myself in saying if you want to REPLACE Column A with separated values, you'll need VBA, actually, you Don't.

Select your cell (or Column) containing the data, go to Data tab, click Text to Columns, choose "Delimited", Next, choose "Comma", Next, click "Finish".