Split email address into 2 fields and remove @xxxxx.com 3

[loveyoursite]

CRV10. I have a field {EMPLOYEE.EMAIL_ADDRESS} that I need to split into 2 seperate fields, removing everything after the @. So, everything before the first . is the firstname and everything after the first . but before the @ is lastname.

Current string: Jane.Doe@company.com

Expected Result seperate field Firstname: Jane
Expected Result seperate field Lastname: Doe

Thanks so much!

 

[synapsevampire]

Try:

First name
split(left({table.field},instr(email,"@")-1),".")[1]

Last Name:
split(left({table.field},instr(email,"@")-1),".")[2]

-k

 

[loveyoursite]

Thanks so much. First Name worked perfectly. Last Name came back with:
"A subscript must be between 1 and the size of the array".

Got any ideas?

 

[synapsevampire]

Could be one of two things, I had a typo:

First name
split(left({table.field},instr({table.field},"@")-1),".")[1]

Last Name:
split(left({table.field},instr({table.field},"@")-1),".")[2]

Or it may be that you don't have a dot, so test:

if instr(left({table.field},instr({table.field},"@")-1),".") > 0 then
split(left({table.field},instr({table.field},"@")-1),".")[2]
else
{table.field}

That way you can see what you have.

-k

 

[dgillz]

This means that the subscript 2 was lsrger than the number of members in the array. Most likely because there was no period "." in the email address. Can you confirm this?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[loveyoursite]

Both of you are correct! I did the test (thank you!) and it brought back a few that did not have the dot. They were simply text@company.com, like brown23@company.com.
Is there a way for it to just show the field contents if there is no dot before the @? Also, would I have to change the firstname formula as well to cover the instances of no dots?

 

[dgillz]

SyapseVampire has answered this for you already. Use the instr() function to test for the existence of a dot.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[synapsevampire]

First name:

if instr(left({table.field},instr({table.field},"@")-1),".") > 0 then
split(left({table.field},instr({table.field},"@")-1),".")[1]
else
left({table.field},instr({table.field},"@")-1)

Last Name:
if instr(left({table.field},instr({table.field},"@")-1),".") > 0 then
split(left({table.field},instr({table.field},"@")-1),".")[2]
else
"last name not specified"

-k

 

[loveyoursite]

Excellent. You get an A+ synapsevampire. You always answer by just giving the requested formula and that's so helpful to those of us who are formula impaired! Thanks again!

 

[loveyoursite]

Oops - another problem. When I try to export the report to .txt, .xls or .pdf, it brings up the firstname formula and stops with this error: String length is less than 0 or not an integer.

Is there any way around this? I need to work with the data in Excel.

Thanks so much!

 

[synapsevampire]

Try:

if len(left({table.field},instr({table.field},"@")-1)) > 0 then
(
if instr(left({table.field},instr({table.field},"@")-1),".") > 0 then
split(left({table.field},instr({table.field},"@")-1),".")[1]
else
left({table.field},instr({table.field},"@")-1)
)
else
"first name not specified"

Last Name:
if len(left({table.field},instr({table.field},"@")-1)) > 0 then
(
if instr(left({table.field},instr({table.field},"@")-1),".") > 0 then
split(left({table.field},instr({table.field},"@")-1),".")[2]
else
"last name not specified"
)
else
"last name not specified"

The difficulty is likely as a result of the field being empty, or the field up until the @ symbol.

You might also eliminate them from the report entirely by using Report->Seelction Formulas->Record and placing:

(
not(isnull({table.field}))
)
and
(
not(left({table.field},instr({table.field},"@") = 0)
)

-k

 

[loveyoursite]

Thanks so much! The revised formula did not work for exporting, I got the same error message (String length is less than 0 or not an integer) with this part of the formula hilighted: left({EMPLOYEE.EMAIL_ADDRESS},instr({EMPLOYEE.EMAIL_ADDRESS},"@")-1)

I tried the "elimination" formula but it kept telling me a ) was missing. Sorry, when it comes to this type of formula I am clueless.

In the detail section, I tried suppressing the record if the email was null but that would not export either - got the same error message.

 

[synapsevampire]

Since "left({EMPLOYEE.EMAIL_ADDRESS},instr({EMPLOYEE.EMAIL_ADDRESS},"@")-1)" appears in many areas, you're not helping to describe where the problem is, please reread your posts before submitting so that they make sense.

Try:

if not(isnull({table.field}))
and
if len(left({table.field},instr({table.field},"@")-1)) > 0 then
(
if instr(left({table.field},instr({table.field},"@")-1),".") > 0 then
split(left({table.field},instr({table.field},"@")-1),".")[1]
else
left({table.field},instr({table.field},"@")-1)
)
else
"first name not specified"

Last Name:
if len(left({table.field},instr({table.field},"@")-1)) > 0 then
(
if instr(left({table.field},instr({table.field},"@")-1),".") > 0 then
split(left({table.field},instr({table.field},"@")-1),".")[2]
else
"last name not specified"
)
else
"last name not specified"

There may be a parenthetical concern here still, I didn't test. The formula is relatively simple in it's parts, don't let the sheer size of it intimidate you, it's comprised of many simple functions, that's it.

Tear it apart and read up on what each section does, you'll grasp it easily.

-k