Insert text before the last item in a string

I am trying to convert an exported SharePoint checkbox list into a plain English sentence. For example, I would like to convert
A1=a;#b;#c;#d
to
This list includes a, b, c, and d.

To do this, I used the function
RIGHT(A1,FIND("#",A1))
to pull out the item following the final #
SUBSTITUTE to replace it with "and " + itself
SUBSTITUTE to replace ;s with ,s and #s with spaces

I tried the formula
=CONCATENATE("This list includes ",SUBSTITUTE((SUBSTITUTE((SUBSTITUTE(A1,(RIGHT(A1,FIND("#",A1))),CONCATENATE("and ",RIGHT(A1,FIND("#",A1))))),";",",")),"#"," ")),".")

It didn't work. The result changes depending on what is in the string, which doesn't make sense to me. Please help! Is there an easier way to do this?

a;#b;#c;#d
This list includes a, b, cand , d.

a;#b;#c;#ddd
This list includes a, b, c, and ddd.

a;#b;#c;#dddddddd
This list includes a, b, c, and dddand ddddd.

Your formula is based on an incorrect assumption.

Originally Posted by jwac

To do this, I used the function
RIGHT(A1,FIND("#",A1))
to pull out the item following the final #

That is not a correct statement.
The find is finding the 1st instance of #, not the last.
The right is returning the last 3 characters because it is a 'happy coincidence' that the last # is the same number of characters from the end as the 1st one is from the beginning.

Based on your example
A1=a;#b;#c;#d
to
This list includes a, b, c, and d.

You can just use
="This list includes "&SUBSTITUTE(A1,";#",", ")
Although this will not include the word "and" as per your example. A minor issue if you ask me.