Move To New Row After Comma

[Nanogirl21]

I have a list of ingredients in cell A1. Each ingredient is separated by a comma (,). How can I move each ingredient to its own row?

EXAMPLE:

ORIGINAL
CELL A1 = w[FONT=&quot]ater, sodium lauroyl methyl isethionate, glycerin

WANTED
CELL A1 = water
CELL A2 = [/FONT][FONT=&quot]sodium lauroyl methyl isethionate[/FONT][FONT=&quot]
CELL A3 = [/FONT][FONT=&quot]glycerin
[/FONT]

 

[Rick Rothstein]

Here is a macro that will do it...

[table="width: 500"]
[tr]
	[td]Sub SplitCommaDelimitedListToIndividualRows()
  Dim Parts() As String
  Parts = Split([A1], ",")
  Range("b2").Resize(UBound(Parts) + 1) = Application.Transpose(Parts)
End Sub[/td]
[/tr]
[/table]

 

[Nanogirl21]

Here is a macro that will do it...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SplitCommaDelimitedListToIndividualRows()
  Dim Parts() As String
  Parts = Split([A1], ",")
  Range("b2").Resize(UBound(Parts) + 1) = Application.Transpose(Parts)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

worked perfectly. thank you.

 

[Rick Rothstein]

Here is a macro that will do it...

[table="width: 500"]
[tr]
	[td]Sub SplitCommaDelimitedListToIndividualRows()
  Dim Parts() As String
  Parts = Split([A1], ",")
  Range("b2").Resize(UBound(Parts) + 1) = Application.Transpose(Parts)
End Sub[/td]
[/tr]
[/table]

In thinking about it, we can make my macro a one-liner...

[table="width: 500"]
[tr]
	[td]Sub SplitCommaDelimitedListToIndividualRows()
  Range("B2").Resize([LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1]) = Application.Transpose(Split([A1], ","))
End Sub[/td]
[/tr]
[/table]