Text Forumla error

[krzysiek]

Hi All,

Hope any of You can help me with below formula?

I am having a problem with creating the formula that returns name and surname when it is in table, and on few occasions there might be an e-mail address (Name.Surname@companyname.com) in this sitation the formula should extract name and surname from e-mail address.

I already created this formula
=IF(ISERROR(FIND("@",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),1)),VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),PROPER(LEFT(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1)&" "&PROPER(MID(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))+1,FIND("@",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1))))

it works to some extent,
when formula finds @ character both IF conditions are correct, but when it does not find @ character the second condition PROPER(LEFT(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1)&" "&PROPER(MID(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))+1,FIND("@",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1))) is rerutning error.

Possibly any of You have an idea how to correct this?


Thank You in advance,

Regards,

Krzysiek

 

[DKaur]

Can you provide a sample of the data?