Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
renjithpl
Specialist
Specialist

Get value between two special characters.

Hi All,

i have a table like below:

EmailidFirst NameLast Name
marguerite.e.sheehan@idea.comMarguerite

Last Name may be blank sometimes, i need to find a formula which will say, if (Last Name is blank, then Last Name should be from email id between '.' and '@')

The problem i have here is, the email id has middle name,

my output should look like:

EmailidFirst NameLast Name
marguerite.e.sheehan@idea.comMargueriteSheehan

i tried something with subfield, but its not giving the right answer.

Any Help?

1 Solution

Accepted Solutions
Not applicable

Quickest way i can think of is :

subfield(subfield('marguerite.e.sheehan@idea.com','.',3),'@',1)

Have Fun,

GM

View solution in original post

2 Replies
Not applicable

Quickest way i can think of is :

subfield(subfield('marguerite.e.sheehan@idea.com','.',3),'@',1)

Have Fun,

GM

swuehl
MVP
MVP

I think this a bit shorter

textbetween('marguerite.e.sheehan@idea.com','.','@',2)

If you want the name capitalized, use

Capitalize(textbetween('marguerite.e.sheehan@idea.com','.','@',2))

and if you have records with middle name and some without, i.e. 1 or two occurences of '.' before the '@', use something like this

=Capitalize(textbetween('marguerite.e.sheehan@idea.com','.','@',

SubStringcount(left('marguerite.e.sheehan@idea.com',index('marguerite.e.sheehan@idea.com','@')),'.')))

Of course you should replace 'marguerite.e.sheehan@idea.com' with your field name.

Regards,

Stefan