SharePoint - Export to Excel and removing # and numbers from lookup and people columns

When a list is exported to Excel from SharePoint you may see entries for lookup columns and People like this

myentry;#32;anotherentry;#43

We can remove these by creating a column next to the column with these entries - say that the above is in column C1

Use this formula in the new column in D1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),";#;#","; "),";#","")

This will remove the #no bits. Copy this column and then paste it over the original column (C) as "Values". Then remove the column you used in the formula (D)