Mandy_
asked on
Excel Formula add SUBSTITUTE(B1;" "."") to formula from splitting displayname
hi,
In Cell "G2" i have this formula
=TRIM(MID(C2,FIND(",",C2,1 )+1,IFERRO R(FIND("(" ,C2,1)-FIN D(",",C2,1 )-1,LEN(C2 )))))
C2 is: Jones, Mike
The result in G2 is "Mike" Firstname
in Cell "H2" i have this formula
=MID(C2,1,(SEARCH(",",C2)) -1))
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.
Mandy
In Cell "G2" i have this formula
=TRIM(MID(C2,FIND(",",C2,1
C2 is: Jones, Mike
The result in G2 is "Mike" Firstname
in Cell "H2" i have this formula
=MID(C2,1,(SEARCH(",",C2))
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.
Mandy
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome. Looks like quite a project you've got going there. Moving to new email servers, are we? :-)
ASKER