Solved

Excel Formula add SUBSTITUTE(B1;" "."") to formula from splitting displayname

Posted on 2014-09-21
4
428 Views
Last Modified: 2014-09-21
hi,

In Cell "G2" i have this formula

=TRIM(MID(C2,FIND(",",C2,1)+1,IFERROR(FIND("(",C2,1)-FIND(",",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
0
Comment
Question by:Mandy_
  • 2
4 Comments
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 100 total points
Comment Utility
For all but the last one, this should work:

=SUBSTITUTE(SUBSTITUTE(TRIM(MID(C3,FIND(",",C3,1)+1,IFERROR(FIND("(",C3,1)-FIND(",",C3,1)-1,LEN(C3))))," ","."),"-",".")

and

=SUBSTITUTE(SUBSTITUTE(MID(C3,1,(SEARCH(",",C3))-1)," ","."),"-",".")

But the 'different' one -> do the numbers belong to the last name? If so, why are they the 'first name' field?
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 400 total points
Comment Utility
For the first name (deep breath):
=SUBSTITUTE(SUBSTITUTE(TRIM(IF(ISERROR(FIND("(",C2)),MID(C2,FIND(",",C2)+2,30),MID(C2,FIND(",",C2)+2,FIND("(",C2)-FIND(",",C2)-3))),"-",".")," ",".")

For the last name (inhale):
=SUBSTITUTE(SUBSTITUTE(TRIM(IF(ISERROR(FIND("(",C2)),LEFT(C2,FIND(",",C2)-1),LEFT(C2,FIND(",",C2)-1)&"."&MID(C2,FIND("(",C2)+1,LEN(C2)-FIND("(",C2)-1))),"-",".")," ",".")

If there are compound last names like:
Washington Carver, George (123)
the last name will be
Washington.Carver.123

Side note:  These look like data from Active Directory, but I too am confused that the number (probably representing identical name listings) is after the comma.  In our system, for example, the numbers are concatenated to the last name (ex., Ray2).


Regards,
-Glenn
0
 
LVL 2

Author Closing Comment

by:Mandy_
Comment Utility
you both are my personal heroes. Thank you muchisimo ;-)
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
You're welcome.  Looks like quite a project you've got going there.  Moving to new email servers, are we? :-)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now