Separate String by Comma and Insert In New Row

[JPatty]

Please help, I need to separate the state abbreviation by comma in Column B and keep the price in Column A aligned with the correct state. Each state abbreviation should be in a new row, and the price needs to as well.

 

[Peter_SSs]

Here is another vba option if you are interested.

Sub SplitStates()
  Dim a As Variant
  
  With Range("A2", Range("B" & Rows.Count).End(xlUp))
    a = Application.Transpose(Split(Join(Application.Transpose(Evaluate(.Columns(1).Address & "&"";""&substitute(" & .Columns(2).Address & ","" ""," & .Columns(1).Address & "&"";"")")), ","), ","))
  End With
  With Range("C2").Resize(UBound(a))
    .Value = a
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Other:=False
  End With
End Sub