Need help completing a formula to find and substitute a character in a text string...

I have a formula which does the following: it finds the last comma followed by a space (, ) in a text string and replaces it with the word "and", and if there isn't a comma followed by a space, it leaves the text string as is.

For example, I have the following words "dog, cat, boy, girl", and this formula changes the text to "dog, cat, boy and girl" as long as there is at least one comma followed by a space.

That formula looks like this: =IF(LEN(B123)-LEN(SUBSTITUTE(B123,",", ""))>=1,IFERROR(TRIM(SUBSTITUTE($B123,","," and",LEN($B123)-LEN(SUBSTITUTE($B123,",","")))),$B123),TRIM(B123))

Here is the addition I want to make:

If there is exactly one comma followed by a space in a text string, it finds that comma and space and replaces it with the word "and". That formula would look like this:

=IF(LEN(B123)-LEN(SUBSTITUTE(B123,",", ""))=1,IFERROR(TRIM(SUBSTITUTE($B123,","," and",LEN($B123)-LEN(SUBSTITUTE($B123,",","")))),$B123),TRIM(B123))

However, I'd like to add to that if there are TWO or greater commas followed by a space, it will find the last comma with a space and replace it with ", and" [a comma, space and the word "and"].

The other formula I have that I need to make this adjustment to which basically does the same thing is this: =IFERROR(SUBSTITUTE(B123,", "," and ",LEN(B123)-LEN(SUBSTITUTE(B123,", "," "))),B123)

But I have the same problem... If there are more than two commas followed by a space, I need it to replace the last ", " with ", and". If there is only one comma followed by a space, it replaces the ", " with " and". And if there are no commas followed by a space, it leaves the text as it is.

Thank you!

assuming cell a1

Formula:

Please Login or Register  to view this content.

its using the instance num of substitute to determine where the " and" is placed

Formula:

Please Login or Register  to view this content.

this will give you the instance num

Another......
Assuming your string is in A1, then

Please Login or Register  to view this content.