The OP specifically asked for a formula solution, so VBA might not be appropriate (possibly a workplace restriction):

Can this be done with formula?