Split a column using comma delimiter

I have an address column that has everything separated with a column eg. street address, 2nd address (Apt etc.), city, state, zip code. I know how to separate them using the delimiter but some of the addresses don't include and 2 address so when I separate them the city ends up going into the 2nd address column and the other 2 are also in the wrong column. I am trying to find a way to get around this but I running into walls. Is there a way to insert a blank 2nd address with a comma if there isn't one in the original column? I've attached a sample of what it looks like and what I would like the output to be to give you a better idea as to what I need.

Thanks for any help.

Process would be:

Count the number of commas.

If one Coma short, then replace the appropriate coma with two commas.

Formula for C2

Formula:

Please Login or Register  to view this content.

Try this

Formula for C2

Formula:

Please Login or Register  to view this content.

Hi,

One way would be to regularise column B with the following in C2 copied down

Formula:

Please Login or Register  to view this content.

Then convert column C to values and use TextToColumns with a comma delimiter

I assume B4 was a typo with a missing comma