Excel Formula add SUBSTITUTE(B1;" "."") to formula from splitting displayname
Posted on 2014-09-21
In Cell "G2" i have this formula
C2 is: Jones, Mike
The result in G2 is "Mike" Firstname
in Cell "H2" i have this formula
The result in H2 is "Jones" Lastname
so far so good but how can replace spaces or - or with "." when the displayname looks like that:
Jones-ryan, Mike Michael Result G2 should be mike.michael and H2 jones.ryan
Jones ryan, Michael Mike Result G2 michael.mike and H2 jones.ryan
Jones, Dimitri Jack Michael Result G2 dimitri.jack.michael and H2 jones
The last one is different
Jones, Michael Jack (12345) Result G2 michael.jack and H2 jones.12345
I tried different formula with =SUBSTITUTE(G2;" ";"") but still not working
Could anyone assist how to solve that, please?
Any help would be greatly appreciated.