InfoPath Dev

I am having difficulty parsing out the first and last names from an email address into a person's name.  I have a field Submitter_email and when a user enters his/her email address I would like to then take that data and enter a First and Last name into another field by concatenating text from the Submitter_email field.

For example:   mickey.mouse@google.com   would equate to Mickey Mouse in the name field

 

Here is what I came up with, but it doesn't quite work because I can't work out how to exclude the part of the address from the @ sign.  Anyway to capitalize the first letters of the name so that I end up with Proper name formatting as well, this part is not critical.

concat(substring-before(".", Submitter_email)," ",(substring-after(".", Submitter_email))

Thanks

This is kind of fun.

Here is the formula.

concat(translate(substring(Submitter_email, 1, 1), "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), substring-after(substring-before(Submitter_email, "."), substring(Submitter_email, 1, 1)), " ", translate(substring(substring-after(substring-before(Submitter_email, "@"), "."), 1, 1), "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), substring-after(substring-after(substring-before(Submitter_email, "@"), "."), substring(substring-after(substring-before(Submitter_email, "@"), "."), 1, 1)))

I used it as the default for the name field but you probably want to use it as a set field value rule.  I would do a check probably and make sure the email is formatted right so that you do not end up with garbage.

To explain, you can nest your substring formulas to get the correct substring, this can get complex but you have to work from the inside out. So the first piece I get the first letter in the string and translate it to a capital. Then I concatenante that with the whole first name (before the ".") and after the first letter.  Then I concatenate a space and use a nested after/before to get the string before the @ and after the . which would be the last name and repeat.

Then the translate function is nice to change the capitalization. I have used it before with data that had been entered in all caps, to convert it for string comparisons.

Good Question

What can I say, but ....Wow!  Thanks for this Clay.  Just to type it out must have taken a good 5 min :)

I'll try it out and let you know how it works out.  I had no clue about the Translate function so I defintely upped my knowledge there as well.

If you use the Insert Function button and copy and paste it doesn't take that long.  And actually doing it inside a form ensured I had it right.

 Glad to help and I think there are lots of people who are not aware of how to accomplish this.

 I may create a blog post and use some color to make it a little easier to figure out.

Also you will need to reupdate the field name.  Paste my text in and then select Insert Field on the first one and select your field. Then you can copy and paste it through out the rest.

Thanks a ton Clay!  It worked great. 

As you suggested I used this function in conjunction with a Rule that sets the Name field value when specific conditions are met.

Follow up: For the function concat in Infopath, how can I include a double-quote? I tried escaping the character, but that doesn't work. I need this in order to dynamically create a query string for filtering with EnumDB.

Thanks!

Hi 

""" might work or try putting the " in a field and referencing it in the formula

Cheers

Thanks for this! Not exactly what I was looking for, but definitely set me on the right path!

Hello,

 This is really great. Only it doesn't work if I try to update the same field.

Actually, I'm looking into formatting a field so that all words (no matter how many), get their first letter capitalized. (Like In This Example). Any help is greatly appretciated.

 

Thanks in advance,

Mircea

This is awesome....and I was really excited to get it working on my form...all the way up until I previewed my form.  My scenario is similar however not exactly like yours. Could you possible shed some light on what I might need to do .  Here's the scoop:

I have a contact selector control and two hidden txt fields on my form.  The two txt fields are hidden and hopefully will hold the First Name portion of the extracted DisplayName (of contact selector value) and the second txt field will hold the last name.  As you probably already know...the DisplayName value of contact selector displays as [LastName],[FirstName].  What I'm doing for now is to just  try and get a successful parse so I'm setting the default value of the hidden txt field to be the value obtained using your highly complex and impressive concat statement.

 So to try and simplify...instead of parsing out the email address of the contact selector control value, I need to parse out and separate the DisplayName portion of the contact selector control value (which displays Lastname,Firstname).  NOTE:  I was able to get the last name to appear in the hidden txt field by using:  
concat(translate(substring(DisplayName, 1, 1), "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), substring-after(substring-before(DisplayName, ","), substring(DisplayName, 1, 1)), " ", translate(substring(substring-after(substring-before(DisplayName, "@"), ","), 1, 1), "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), substring-after(substring-after(substring-before(DisplayName, "@"), "."), substring(substring-after(substring-before(DisplayName, "@"), "."), 1, 1)))

I hope this makes sense and you or someone can help cuz the ability to get this working drives whether a form of ours gets put into production or not.  Thank you in advance for any help.

In most companies DisplayName is capitalized already. So your expression should be much simplier. For example

LastName: substring-before(DisplayName," ") FirstName: substring-after(DisplayName," ")

Hi Alec,

Could you help me by maybe filling in the blanks for me....I'm a rookie at all of this just trying stuff as I go.  Unfortunately not a lot of it makes sense to me:

so per your suggestion I should have the following?
concat(translate(substring-before(DisplayName," "))) - which is to get the last name...and then a separate one:
concat(translate(substring-before(DisplayName," "))) - to get the first name....but how would I go about putting them together.

I tried applying your response and the forumla checker kept erroring on the "LastName" and "FirstName" parts.

The LastName and FirstName are your hidden fields they are not part of the formula. And here are the expressions to set your hidden fields to the new values. You will need two separate actions for each of your field.

substring-before(DisplayName," ") - which is to get the last name...

substring-after(DisplayName," ") - to get the first name

Hi, I have a similar issue

Displayname is Lastname, Firstname (XX)

Need to parse out the email address - Firstname.Lastname@domain.com

Got most of it working from the posts here but now need to parse out firstname without the (XX) (note that XX is a two character country code)